Filtering with a combo box (97)

[roger-trigg]

Excuse a non-programmer daring to enter but I'm trying to filter a set of records using a combo box. Followng an article in Inside Access (vol3, 7, page 5) I've got the code below as the after update event for the combo box
:
DoCmd ShowAllRecords
DoCmd ApplyFilter , "[Supplier] = Forms!SupplierItems!Search"

where Supplier is the field I want to filter on the form 'SupplierItems' and Search is the name of the combo box.

When I complie this code I get an error 'Variable not defined' with 'ShowAllRecords' highlighted. I guess my model was written in Access 2 days. is this my problem? Does anyone have the fix please?

[Caz]

Your code is incomplete in that your are doing 2 commands at the same time, your either want to display all records, or you want to filter the records. Try


' Return record(s) that match value selected in Search combo box.
If Len(Me!Search) = 0 Then
DoCmd.ShowAllRecords
Else
DoCmd.ApplyFilter , "[Supplier] = Forms!SupplierItems!Search"
End If

NB: Assumes search is the combo box field on the current form.

[Francois]

you have to add dot after the domcmd and split the where condition as follow

<pre>DoCmd.ShowAllRecords
DoCmd.ApplyFilter , "[Supplier] = '" & Forms!SupplierItems!Search & "'" </pre>


If supplierItem is the name of your form and search is the name of the combo, even simpler:
<pre>DoCmd.ShowAllRecords
DoCmd.ApplyFilter , "[Supplier] = '" & Me!Search & "'" </pre>


Even the line DoCmd .ShowAllRecords is not necessary

[roger-trigg]

Bang on target, Francois. Many thanks indeed.
Roger

[roger-trigg]

Thanks for the repsonse, Caz, but I followed Francois on this occasion and got a result.
Roger

[Shane Sargent]

Actually it'd be "Dot on target.". Ba dum dum!

(A little VBA humor there, very little: !'s are referred to as Bangs, .'s are referred to as Dots.)