Filter By Form at Runtime

Question

Since I can't use the Filter By Form feature at runtime, I have created the following:

• an unbound form called frmContactsSearchCriteria, that will stay open when the frmContacts is opened
• a query called qryContactsSearchCriteria, which will be the record source of frmContacts

Simple enough. The criteria of each field in the query is forms!frmContactsSearchCriteria.txtLastName, forms!frmContactsSearchCriteria.txtFirstName, and so forth.

This works great if ALL THE FIELDS in frmContactsSearchCriteria have a value. But often only a few fields have criteria.  Maybe they only want to view contacts in Chicago. .. So they type Chicago in forms!frmContactsSearchCriteria.txtCity and apply the filter.  How do I get the query to ignore all the fields in frmContactsSearchCriteria that are blank?

Answer 1

The nearest example I can give you is one where the unbound controls to restrict the results returned are in the header of a bound form, rather than in a separate dialogue form.  The only difference is that the current form is requeried rather than a separate form.  The file is 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.

In this little demo file take a look at the option to 'Open simplified form for 'drilling down' through hierarchies to return records.'   The unbound controls in this form are correlated combo boxes in which a selection in one limits the available selections in the next down the line, but that's not relevant.  They'd work on exactly the same way if they were independent of each other.  The form's RecordSource is the following query, which is set up as Dirk described ealier:

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

Parishes.ParishID  

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_Simple]![cboGotoCounty]

    OR [Forms]![frmDrillDown_Simple]![cboGotoCounty] IS NULL)

AND (Districts.DistrictID=[Forms]![frmDrillDown_Simple]![cboGotoDistrict]

    OR [Forms]![frmDrillDown_Simple]![cboGotoDistrict] IS NULL)

AND (Parishes.ParishID=[Forms]![frmDrillDown_Simple]![cboGotoParish]

    OR [Forms]![frmDrillDown_Simple]![cboGotoParish] IS NULL)

ORDER BY County,District,Parish;

The form is requeried in the AfterUpdate event of each combo box:

Private Sub cboGotoCounty_AfterUpdate()

    ' set district and parish combo boxes to Null

    ' and requery controls to show districts in

    ' selected county

     Me.cboGotoDistrict = Null

     Me.cboGotoDistrict.Requery

     Me.cboGotoParish = Null

     Me.cboGotoParish.Requery

     ' requery form to show locations

     ' in selected county

     Me.Requery

End Sub

Private Sub cboGotoDistrict_AfterUpdate()

    ' set parish combo boxes to Null

    ' and requery control to show parishes in

    ' selected district

     Me.cboGotoParish = Null

     Me.cboGotoParish.Requery

     ' requery form to show locations

     ' in selected district

     Me.Requery

End Sub

Private Sub cboGotoParish_AfterUpdate()

     ' requery form to show locations

     ' in selected parish

     Me.Requery

End Sub

The only line in each your need to concern yourself with is:

     Me.Requery

The other lines correlate the controls.

Answer 2

Thanks so much!

Answer 3

Thank you. Can you point me to some examples of this complete code as a Private Sub? Not sure how to start it...

Answer 4

Following on from Dirk's reply, which is spot on, when designing the query be absolutely sure that you write the WHERE clause and save the query in SQL view.  If you switch to design view before you save it Access will move things around.  At best the logic will be obscured, but you might well find that the query has become too complex to open.  Where a database uses complex queries I will save their SQL statements to a separate text file as part of the application's documentation.  That way you have something to fall back on if things do go horribly wrong.

Answer 5

Generally speaking, you have to add an "Or Forms!frmContactsSearchCriteria!ControlName Is Null" condition to each criterion in the query.  So instead of this WHERE clause in the query:

    WHERE LastName = [Forms]![frmContactsSearchCriteria]![txtLastName]

        AND FirstName = [Forms]![frmContactsSearchCriteria]![txtFirstName]

        AND ...

you would have this:

    WHERE (LastName = [Forms]![frmContactsSearchCriteria]![txtLastName]

                  OR  [Forms]![frmContactsSearchCriteria]![txtLastName] Is Null)

        AND (FirstName = [Forms]![frmContactsSearchCriteria]![txtFirstName]

                 OR  [Forms]![frmContactsSearchCriteria]![txtFirstName] Is Null)

        AND ...