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?
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
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.