Access VBA open form and filter sub form in it

Murray Brown
Murray Brown used Ask the Experts™

What Access VBA would I use to open a  form and at the same time filter the sub form in  it?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Adjust the MasterLinkFields and ChildLinkFields to hold the value to filter by and the field to filter on, respectively.
ste5anSenior Developer

It solely depends on your use-case and what the form/subform represents and whether the main form is data bound or not and when, where and how the filter criteria is determined.

Possible approaches:
- use the master/child approach as Gustav wrote
- use a [Form]!.. reference in the data source of the subform
- use TempVars
- use proxy function(s) to return the filter values in the subform
- change the data source of the subform
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
and note that the master link can point to a control on the main form as well as a field.  That can come in handy at times.  The other way you can handle this is to change the subforms recordsource on the fly:

  Me!mysubformcontrolname.Form.Recordsource =

or set it's filter property:


You also need to turn the filter on:

Me!mysubformcontrolname.Form.FilterOn = True

John TsioumprisSoftware & Systems Engineer
Simple Open the form in interest
DoCmd.OpenForm "NameOfForm"

Open in new window

and then in the Load event  of NameOfForm just
Form_Name_Of_SubForm.Filter = YourFilter
Form_Name_Of_SubForm.FilterOn = True

Open in new window

Murray Developer



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