asked on

Access command button

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.

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

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

clock1

ASKER

Dale,

Access is throwing a compile error:

"Invalid use of Me keyword"

clock1

ASKER

My error, forgot to declare this as a Private function

Dale Fye

You also need to put the function in FormB's code module, not in a shared (standard) code module.