query subform by command button

[Guest]

I am making a directory, which, on the main form, will have command buttons
"A", "B", "C"... etc. and when the user clicks on one of these buttons, the
field [customername] in the subform [customers subform] will be filtered by
that letter. Do I make a query for each letter (And if so, how do I make it
run?), or do I ApplyFilter on each command button?

 

[Marshall Barton]

I am making a directory, which, on the main form, will have command buttons
"A", "B", "C"... etc. and when the user clicks on one of these buttons, the
field [customername] in the subform [customers subform] will be filtered by
that letter. Do I make a query for each letter (And if so, how do I make it
run?), or do I ApplyFilter on each command button?

The simple approach is to use the subform's Filter property:

Me.subformcontrol.Form.Filter = +
& "Left([customername],1) ='" & theletter & "' "
Me.subformcontrol.Form.FilterOn = True

But there are glitched in the Filter property, especiall if
you try to use it on the subform AND on another subform or
the main form.

The old tried and true approach is to (re)construct the
subform's record source query:

strSQL = "SELECT . . . FROM . . . WHERE " _
& "Left([customername],1) ='" & theletter & "' "
Me.subformcontrol.Form.RecordSource = strSQL

 

[Guest]

I don't quite get it. I make a sub in the code of the sub(?)form like this:

Private Sub FilterCustomer()

With Me.[customers subform].Form
.Filter = "[Customername] LIKE '" & Screen.ActiveControl.Caption &
"*'"

.FilterOn = True
End With

End Sub

and then I already have the captions of the buttons named by each letter

and then I paste into the "On Click"(?) property of the buttons, this:

=FilterCustomer()

Sorry, I'm lost.

I think my syntax is incorrect on this line:
.Filter = "[CustomerName] LIKE " & Screen.ActiveControl.Caption & '*'"
Should be
.Filter = "[CustomerName] LIKE '" & Screen.ActiveControl.Caption & "*'"

The easier way to do this is to create a sub that does the filtering and call
it from each button:

Private Sub FilterCustomer()

With Me.MySubForm.Form
.Filter = "[CustomerName] LIKE " & Screen.ActiveControl.Caption &
'*'"
.FilterOn = True
End With

End Sub

To use this, make the Caption Property of each button the letter it stands
for and put this in the Click Event text box of the Properties Dialog. No
need to use any builder, just paste it into the text box:

=FilterCustomer()

I am making a directory, which, on the main form, will have command buttons
"A", "B", "C"... etc. and when the user clicks on one of these buttons, the
field [customername] in the subform [customers subform] will be filtered by
that letter. Do I make a query for each letter (And if so, how do I make it
run?), or do I ApplyFilter on each command button?

 

[Marshall Barton]

I don't quite get it. I make a sub in the code of the sub(?)form like this:

Private Sub FilterCustomer()

With Me.[customers subform].Form
.Filter = "[Customername] LIKE '" & Screen.ActiveControl.Caption &
"*'"

.FilterOn = True
End With

End Sub

and then I already have the captions of the buttons named by each letter

and then I paste into the "On Click"(?) property of the buttons, this:

=FilterCustomer()

This thread is a mess on my system and I ca't tell for sure
who you are replying to, probably Klatuu(?).

In any case the procedure needs to be in the main form.
That's where the buttons are, right?