Filter using multiple criteria

Maestro

Registered User.
Local time
Today, 00:55
Joined
Jun 16, 2001
Messages
21
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!
 
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.
 
Dim strFilter As String
strFilter = "[Surname] = '" & Me.srcSurname & "' AND [City] = '" & Me.srcCity & "'"
DoCmd.ApplyFilter , strFilter
 
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).]
 
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])
 

Users who are viewing this thread

Back
Top Bottom