Filter using multiple criteria

[Maestro]

I have a form based on a query. I am trying to create a search system which uses filters to narrow down the number of records displayed. At present though, it can only filter based on one criteria at a time. I have each text or combo box with the following code as the after_update event:

Dim strFilter As String
strFilter = "[Surname] = '" & Me.srcSurname & "'"
DoCmd.ApplyFilter , strFilter

How can I turn this into something that enables the user to select several criteria, for example to show only members with the surname "Smith" *AND* who live in "Manchester"?

Ideally I'd rather not have a separate 'search' button with everything set up so that the filter is applied only with after_update events, but that depends on how much more complicated that would make life for me!

 

[Rich_Lovina]

If the user is going to require this, then why not concentrate on using options under a Query as opposed to a Form. The filter in Access has the very limitation you allude to. the simple Query Wizard would get youir answer much faster and with less pain.

 

[miki]

Dim strFilter As String
strFilter = "[Surname] = '" & Me.srcSurname & "' AND [City] = '" & Me.srcCity & "'"
DoCmd.ApplyFilter , strFilter

 

[Chris RR]

What I usually do is to create in filter in the After Update for each combo box, then make the user click a separate "Filter the Records" button to actually apply the filter.

The code in each individual "after Update" sub usually looks something like this:
txtCust = Me.Cust
If txtCust <> "" Then
If theFilter = "" Then
theFilter = "[Cust_No] = '" & txtCust & "'"
Else
theFilter = theFilter & " AND [Cust_No]= '" & txtCust & "'"
End If
End If

And the button has code like this:

If theFilter <> "" Then
DoCmd.ApplyFilter , theFilter
theFilter = ""
txtCust = ""
txtWhatever = "" 'some other field I was filtering on
End If

Then, give the user a "Show All Records" button:
DoCmd.ShowAllRecords

[This message has been edited by Chris RR (edited 11-08-2001).]

 

[rschultz]

how about having two comboBoxes, each linked to a different query or table that has the different caagories you want and then have a select statement like this:

SELECT * FROM [weekly sales]
WHERE (([weekly sales].market)=[Forms]![Query menu]![combo7])
AND (([weekly sales].commodity)=[Forms]![Query menu]![combo11])