How to create multiple connected search combo boxes to filter a form?

Question

I have a form with 10 columns, and for 5 of them (Project_Phase, Contract, Design_DPM, AMM/UCC, 1_or_2 stat) I want to add connected drop list combo boxes to filter the records and display data as selected in the combo boxes.

I know how to make multiple drop list combo boxes that filter the whole form based on the selection of one value from one column. For example, "Contract" combo box has options: signed, not signed. If I select "signed" it will display all the records that have "signed". and If I filter another column it will cancel the previous filter and display records relevant only to that selection from that column.

But what I want is the ability to filter using any number of filtering options from the 5 columns I mentioned. For example, if I want to see the records that are ("signed" under "Contract") and in ("proposal" under "Project_Phase") and ("a certain DPM" under "Design_DPM"). And after filtering I want to be able to clear the filters and see all the records again, as I am using this form to display all my records for users. I do not want it to be cascaded, as I might want to filter using only one column or more or all. And I do not want it to be a query or use the basic filtering in datasheet view.

Sorry for the lengthy explanation, if something is not clear I will explain further. Thank you for your efforts.

Sounds like what you need is to: (a) have each combobox have an '*' or 'All' sorted to the top; then (b) implement Event code for each combobox that will review all combo's and generate the correct form filter (or create a new rowsource) by combining all comboboxes.. Does this sound ok? — Wayne G. Dunn
Pardon me I did not understand some of the technical terms you mentioned because I am basically new to VBA coding. But I believe you got it right. Although, allow me to elaborate again just to make sure we are on the same page. — Abd.Y
What I need is: a) For each combobox to have a unique list "no duplicates" of the values in the column this combobox will filter (I know how to do this) b)Yes, implement an AfterUpdate Event code, that when I select a value from one or more comboboxes it will filter the form accordingly. So I can filter my form using one combo or more. c) The ability to clear all the filters and see the full form data again. — Abd.Y

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2016-01-23T17:33:55

Here is one way to implement a form filter using multiple comboboxes.

Create a table named 'tblWildcard'; one field named 'Wildcard', save the table.
Enter one record with an asterisk as the value

Create the queries for your comboboxes like:

SELECT DISTINCT Table1.flda
FROM Table1
ORDER BY Table1.flda
UNION Select wildcard from tblwildcard

Save a query like the following SQL to be the Row Source for your form (i.e. qryFormA):

SELECT Table1.Flda, Table1.Fldb, Table1.Fldb 
FROM Table1
WHERE (((Table1.Flda) Like [Forms]![frmForm1]![cboFlda]) 
AND ((Table1.Fldb) Like [Forms]![frmForm1]![cboFldb]) 
AND ((Table1.Fldc) Like [Forms]![frmForm1]![cboFldc]))

In the After Update event for each combobox add the following code:

Me.Recordsource = "qryFormA"

Sometimes the 'Me.Rowsource.' may not work when changes are made (weird issue!). If so, do the following:

Application.Echo False
Me.RecordSource = vbNullString                    
Me.RecordSource = "rowVwFilter25"
Application.Echo True

Finally, if a multi-user environment, and people may be adding records that will need to be included in your comboboxes, use the combobox Before Update event and add the following code:
```
Me.cboFldA.Requery
```

How to create multiple connected search combo boxes to filter a form?

2 Answers