Solved

Access command button

Posted on 2014-11-28
5
274 Views
Last Modified: 2014-11-28
FormB displays records whose source is QueryA.
QueryA returns the following records sorted alphabetically on Field1:

Action
Add
After
Age
Back
Before
Build
Call
Clear

I have created command buttons on FormB captioned A,B,C,...one command button for each letter in alphabet.
How can I trigger an event so that when a command button is pressed (example: B), only records beginning with the letter "B" in Field1 are displayed in FormB?  Subsequently, I will want to press another command button, clear the form and display only those records where Field1 records begin with the letter corresponding with the caption of the command button pressed.
0
Comment
Question by:clock1
  • 3
  • 2
5 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40470816
Well, if you are going to do this with buttons, your best bet would be to create a single button, and set the caption property of that button to "A", and the property of the Click event of the button to:  =fnFilterForm()

Then you create a function in the forms code module that looks like:

Private Function fnFilterForm()

    Dim strFilter as string

     strFilter = "[Field1] LIKE '" & screen.activecontrol.Caption & "*'"
     me.filter = strFilter
     me.filterOn = true

End Function

Open in new window

Then you would copy that command button, once for each letter of the alphabet, and simply change the caption from "A" to the appropriate letter.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40470821
But personally, I prefer to either use a combo box, or a textbox to save screen realestate.  For a combo box, I would set the RowSourceType to ValueList (although I rarely do this), and set the RowSource to: A;B;C;D;E;...;Z.  Then I would use the combo boxes AfterUpdate event:
Private Sub cbo_Filter()

    Dim strFilter as string

     strFilter = "[Field1] LIKE '" & me.cbo_Filter & "*'"
     me.filter = strFilter
     me.filterOn = true

End Sub

Open in new window

When I need more flexability than filtering on just the first letter, generally add a textbox (txt_Filter) and a command button (cmd_Filter).  I then use the cmd_Filter_Click event

Private sub cmd_Filter_Click()

    Dim strFilter as string

     if me.txt_Filter & "" = "" then
        me.FilterOn = false
     Else
          strFilter = "[Field1] LIKE '" & me.txt_Filter & "*'"
          me.filter = strFilter
          me.filterOn = true
     end if

End Sub
0
 

Author Comment

by:clock1
ID: 40470827
Dale,

Access is throwing a compile error:

"Invalid use of Me keyword"
0
 

Author Comment

by:clock1
ID: 40470835
My error, forgot to declare this as a Private function
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40470839
You also need to put the function in FormB's code module, not in a shared (standard) code module.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now