Access VBA Set Filter on SubReport and open main report


I have an Access report called "rptInvoice" that has a sub report called "Invoice_Details_SubReport".
I need to write VBA code to set the filter on the Sub Report to "InvoiceID = 48" and then open the main report.
The image below shows my attempt at the code so far. What am I doing wrong?

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

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

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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You're setting the FilterName argument (which expects the name of a Query). Instead, set the WhereCondition argument:

DoCmd.OpenReport "rptInvoice", , , "[Invoice ID]=48"

However, trying to set the the Filter of a Subreport is somewhat against the grain. You generally set the main report filter, and then your report would show all the records associated with that main record. You may be able to do this - I don't know, since it's not a standard process - but in general doing something like this would point to an incorrectly built Report, or table structure issues.

Also, this line:


won't work. A Report or Form being used as a Subreport or Subform won't be in the Report or Form collection. You have to refer to the parent object, then the Subform or Subreport object, then the "object", and then the property:

Reports!Parent_Report!Report_Subform_Object.Report.Filter = YourFilter

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
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Murray,

as Scott alluded to with "against the grain" ... perhaps rethink the logic and use more of what is built-into Access?

in the design view of the main report, set LinkMasterFields and LinkChildFields for the subreport control on InvoiceID

The LinkMasterFields property can be a control name

LinkChildFields is the name of a field in the subreport RecordSource (best if it is also ON the subreport in a control even if it is not visible).

If the link fields are set, then your code does not need to do anything special for the subreport to show the right records -- just open the report, as you are doing (instead of 48, you could get this value from the form for the invoice displayed in the record with the focus)

have an awesome day,
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
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.