Multi text box filter on continuous form

Question

I am using Access 2010. I have a continuous form set up that is based off a query of all assets. I have four fields included on the form from three different tables. I set up three text boxes on the form to filter the assets. I set a macro for each of the text boxes to filter by whatever you type in them. They all work individually but I would like them to all work together. i.e. If only one boxes has characters in it, then that's the result's you would get or if there are characters in two or all three boxes then the results would filter down to show only items that meet all three criteria. Is it possible to join the macros to make this happen?

Answer 1

Thank you both for answering. I am just back from a holiday so I will work on it this week.

Answer 2

You can do this with a minimum of macro actions or VBA code by basing the form on a query which references your text boxes as parameters.  The following is an example which allows the user to drill down through the geographical hierarchy of UK counties, districts and civil parishes.  In this case the controls are combo boxes, but that's immaterial:

SELECT Counties.County, Districts.District, Parishes.Parish, Locations.Location

FROM ((Counties INNER JOIN Districts ON Counties.CountyID=Districts.CountyID)

INNER JOIN Parishes ON Districts.DistrictID=Parishes.DistrictID)

INNER JOIN Locations ON Parishes.ParishID=Locations.ParishID

WHERE (Counties.CountyID=Forms!frmDrillDown!cboGotoCounty

OR Forms!frmDrillDown!cboGotoCounty IS NULL)

AND (Districts.DistrictID=Forms!frmDrillDown!cboGotoDistrict

OR Forms!frmDrillDown!cboGotoDistrict IS NULL)

AND (Parishes.ParishID=Forms!frmDrillDown!cboGotoParish

OR Forms!frmDrillDown!cboGotoParish IS NULL)

ORDER BY Counties.County, Districts.District, Parishes.Parish

The important thing here is that not only does the query test for the value in a column equalling that of the relevant control in the form, but also for the parameter (the control) being NULL, i.e. the control has been left empty.  The county combo box for instance is referenced in the query's WHERE clause with:

(Counties.CountyID=Forms!frmDrillDown!cboGotoCounty

OR Forms!frmDrillDown!cboGotoCounty IS NULL)

Note how the OR operation is parenthesized.  This is important as it forces the operation to evaluate independently of the AND operations.  The way it works is that if the value in a row equals that of the control in the form the first part of the OR operation will evaluate to True, so that row will be returned; if the control IS NULL then the second part of the OR operation will evaluate to True regardless of the value in the column, so all rows will be returned.

By doing the same for each control, and tacking the parenthesized OR operations together with AND operators, only those rows for which all three of the parenthesized OR operations evaluate to True will be returned.

To cause the form to show the rows restricted on the basis of the selections in the combo boxes it is necessary to requery the form.  This is done in my case in the AfterUpdate event procedure of each of the three combo boxes with the line:

    Me.Requery

but, rather than VBA, you could use a macro which requeries the form as the After Update event property of each control.

One thing to note is that the WHERE clause of the query should be entered in SQL view, and the query saved in SQL view.  If you switch back to query design view and save the query Access will move things around significantly.  At best the underlying logic will be obscured, but the query could become too complex to open.

The key thing about this approach is that the logic is so simple, it's just a case of testing, in the case of each control, for:

(SomeColumn = <some parameter>

OR <some parameter>  IS NULL)

As many parenthesized operations like this as necessary can be tacked together with the AND operator.  Given good table design, in particular good indexing of the columns in question, performance is generally very good in my experience.

PS:  if, rather than entering exact values into the text boxes, you are using pattern matching, it's merely case of using the LIKE operator and the asterisk wildcard character in the query, e.g.

(Counties.County  LIKE "*" & Forms!frmDrillDown!txtGotoCounty & "*"

OR Forms!frmDrillDown!txtGotoCounty IS NULL)

Answer 3

I would think you need to remove these macros and instead write a new macro for a new Apply Filter button that user will click after making one or more selections. You may also want to add a Clear Filter button.