Avatar of DGWhittaker
DGWhittaker
 asked on

Filtering or Querying specific records in a Form

How do I filter the records in a form to a specific date, date range, or field value upon loading the form?
Thanks!
Dennis
Microsoft Access

Avatar of undefined
Last Comment
Anders Ebro (Microsoft MVP)

8/22/2022 - Mon
Anders Ebro (Microsoft MVP)

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


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

Open in new window

Scott McDaniel (EE MVE )

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.
DGWhittaker

ASKER
Thanks!
As I am pretty new to this, I could use just a tad more guidance.
Where do I place the DoCmd.OpenForm code?
Dennis
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Anders Ebro (Microsoft MVP)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.