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.

Answer 1

Hi. Just a thought... Have you tried using the DoCmd.ApplyFilter method? Hope that helps...

Answer 2

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.

Hi Abd Y,

What I typically do in those cases is to build the where-part of an SQL-string depending on the value you want to use, by looping through the combo boxes.

In the above example that would be:

    where_string = " AND Contract = 'signed' AND Project_Phase = 'proposal' AND Design_DPM = 'a certain DPM'"

If you want to use the where_string as a filter, skip the first AND.

But you can also replace the where-part in the RecordSOurce of the view. In that case you replace the first AND by WHERE, and assign the result to the RecordSource of the datasheet view.

If you want to select all records, the where-part would be a ZLS:  ""

Imb.

Answer 3

You might like to take a look at ComboDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes two forms for 'drilling down' through a hierarchy, a simple one and a more complex one.  The simple one is probably a suitable model in your case.  It uses, as its RecordSource, a query which references the unbound combo box controls in the form header using the following syntax:

WHERE (SomeColumn =  Forms!FormName!SomeControl
OR Forms!FormName!SomeControl IS NULL)
AND (SomeOtherColumn =  Forms!FormName!SomeOtherControl
OR Forms!FormName!SomeOtherControl IS NULL)
AND……etc

Note that the parentheses are crucial to force the OR operations to evaluate independently of the AND operations.

In the AfterUpdate event of each unbound combo box requery the form with:

Me.Requery

To clear the combo boxes and show all rows simply set each combo box to Null and then requery the form, e.g. in the Click event procedure of a 'Show All' button.

The fact that the combo boxes in my demo are correlated, so that selecting a value in one limits the available selections in the next one down in the hierarchy, is probably not relevant to your situation, so you don't need to include code in the control's AfterUpdate event procedures to requery the other combo boxes, only to requery the form.

---

Ken Sheridan, Stafford, England

Answer 4

Hi, Abd Y

According to your description, I suggest that you could use the Filter property to specify a subset of records to be displayed when a filter is applied to a form, reportquery, or table, when combo

box OnChange event was triggered. You could refer to below code:

Private Sub Store_Selection__Change() 

Me.Filter = "Store_ID=" & Me.Store_Selection.Column(0)

Me.FilterOn=True

End Sub 

For more information, click here to refer about Form.Filter Property (Access)

Answer 5

No but I found another way. I appreciate your help

Answer 6

I found exactly what I wanted here (http://allenbrowne.com/ser-62.html), and part of it is similar to what you said about looping. I really appreciate your help

Answer 7

Thank you I appreciate your help. But I found exactly what I wanted here (http://allenbrowne.com/ser-62.html)

Answer 8

I appreciate your help, thank you. But I found exactly what I wanted here (http://allenbrowne.com/ser-62.html)

Answer 9

My question is answered. Thanks for the help everyone.

I found exactly what I wanted here (http://allenbrowne.com/ser-62.html)