Filter or setting the RecordSource

[JKpeus]

I have a form that shows my tblCompanies.
I have a field in tblCompanies that sets the lngCompanyType (supplier, customer, etc.)
Also on the form I have a "filter" comboBox (fltrCompanyType) that allows the user to filter by lngCompanyType. This works fine with the following code:

Private Sub fltrCompanyType_Change()
    Form_frmCompanyContacts.RecordSource = "SELECT * FROM tblCompanies WHERE tblCompanies.lngCompanyType = " & fltrCompanyType.Value
End Sub

I would like to allow the user to "filter" by more fields. For instance:
Only show the Companies that are "Suppliers" and located in "Michigan" (this is the strCompanyState field in the tblCompanies table).

Eventually I will have up to four fields that the user could "filter" by. I can apply one filter at a time, but I don't know how to "filter" the already "filtered" data. Any ideas?


Thanks,
Jason

 

[jfgambit]

Jason:
Take a look at the attached database. It allows for multiple filtering based on exact or partial entries.

It passes the parameters to the query by re-writing the QueryDef of the Query. This can then be used as the Row Source or Record Source of a listbox, form, combobox, or report.


HTH

 

[JKpeus]

Cool, thanks jfgambit.

I'll take a look at it.

 

[judgehopkins]

for jfgambit

Nifty searcher! Thanks!

 

[jfgambit]

Updated version...

 

[totomalas]

Updated version...

very nice work indeed,,,i want to ask how did u make the switchboard fixed once the db is opened?