Filter Form by Search Criteria

[littleclayjar]

I have a filter form that has three search criteria for suppliers which is selected by paired list boxes - Country, Technology, Packaging. So when a country is selected, the country moves from Lst_Country_Source to Lst_Country_Dest and changes a field called "InSelectedList" in the Countries table to YES. The same goes for the other two criteria.

I would like to set filters for just the criteria that are selected and have the results filtered and displayed on another form called FRM_Suppliers_Main, which currently contains all the suppliers.

How do I even start and would I have to set different If statements for each combination of criteria (Country & Technology; Country & Packaging; Packaging; etc)? I also made queries that already do the filtering but I'm not sure whether I can just incorporate them into the VBA or even how.

Thanks for the help in advance and let me know if you need more clarification.

 

[Remou]

Create the recordsource from the filter form. For example:

strSQL="SELECT ID, Country FROM tblTable " _
& "WHERE Country IN " _
& "SELECT Country FROM Countries " _
& "WHERE InSelectedList"
Forms!frmList.RecordSource = strSQL