Filtering or Querying specific records in a Form

DGWhittaker used Ask the Experts™
How do I filter the records in a form to a specific date, date range, or field value upon loading the form?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The forth (4) argument (the WhereCondition) of the DoCmd.OpenForm is what you would normally use to pass a filter string.
Docmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

Open in new window

DoCmd.OpenForm "Employees", , ,"LastName = 'King'"

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Anders has shown you how to filter records when opening a form, but if you want to filter records in an already-open form, then you'd have to do things a little differently.

Many times I'll add filtering controls to the Form's Header, and then set the Form's Filter property based on the user's entries into those fields. For example, if I want the user to be able to search for a LastName in the form, I'd do this:

1. Add a textbox named "txSearch_LName" and a command button named "cmSearch"
2. Add code to the cmdSearch like this:

If Len(Me.txSearch_LName)>0 Then
  Me.Filter = "Last_Name LIKE '" & Me.txSearch_LName & "*'"
  Me.FilterOn = True
End If

This would filter the form's Recordset to show those records whose Last_Name data was like the data I entered in the txSearch_LName field.

Note you can get very, very complex with form filtering, so be sure you know exactly what you (and your users) will need before adding filtering processes.


As I am pretty new to this, I could use just a tad more guidance.
Where do I place the DoCmd.OpenForm code?
Well it could be placed just about anywhere you want to open a form from.

For example, I might have a list of customers, displayed in a datasheet or continous form. In the click event of the textbox bound to the id field, I might have such code:
Private Sub txtID_Click()
  DoCmd.OpenForm "frmCustomer", , ,"CustomerID=" & me.txtID
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial