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
DGWhittakerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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 (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
DGWhittakerAuthor Commented:
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
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.