VBA & Macro Problems

How to Email a Filtered Report from a Nested Subform in Access VBA

Camila MilosovichCamila Milosovich Nov 4, 2026 871 views

Question details

The user is attempting to open and email a filtered report from a command button located on a nested subform but encounters a compile error.

How to Email a Filtered Report from a Nested Subform in Access VBA
Product
Microsoft Access
Device & OS
not provided
Scenario
Clicking an Access command button to open or email a report that is filtered by a specific record on a nested subform.
Observed behavior
An 'Argument not optional' compile error is generated, preventing the report from opening or being emailed.
Before you start

Verify the exact names of your main form, primary subform control, and nested subform control, as precise naming is required for VBA and query referencing.

Solution 1Recommended

Correct VBA Syntax and Update Query Form References

Fix the compile error by removing empty arguments in your VBA code and applying the correct hierarchical reference syntax in the report's query.

The 'Argument not optional' error is typically caused by a stray comma in the DoCmd statement, which makes Access expect a parameter that hasn't been provided. Additionally, reports triggered from nested subforms must have their underlying query criteria mapped explicitly through every layer of the form hierarchy.

1
Fix the DoCmd.OpenReport Syntax

Open the VBA Editor (Alt + F11) and locate the click event for your command button. Check the 'DoCmd.OpenReport' line and remove any extra trailing commas. Ensure all required arguments are either provided or properly skipped without leaving a dangling comma at the end.

2
Update the Report Query Criteria

Open your report's underlying record-source query in Design View. In the criteria row for the filtering field, enter the full nested path: Forms!MainFormName!FirstSubformName.Form!NestedSubformName.Form.ControlName.

3
Implement the DoCmd.SendObject Method

Below your OpenReport command (or replacing it if you only want to email), use 'DoCmd.SendObject acSendReport, "YourReportName", acFormatPDF' to attach the newly filtered report to an email.

Correct VBA Syntax and Update Query Form References
Subform Referencing: When referencing subforms, the '.Form' property is essential. It tells Access that you are targeting the actual form object contained within the subform control, not the control frame itself.
Free Microsoft Office alternative

Looking for a Lightweight Alternative to Microsoft Office?

While Microsoft Access handles complex relational databases, WPS Office provides a free, lightweight alternative for your daily document, spreadsheet, and presentation tasks with seamless Microsoft format compatibility.

  1. 1. Download the Installer: Visit the official WPS Office website and download the free installation package.
  2. 2. Install WPS Office: Run the setup file and follow the simple on-screen instructions to install the suite.
  3. 3. Open Your Files: Launch WPS Office and directly open your existing .docx, .xlsx, or .pptx files to start working immediately.
Fully compatible with Microsoft Word, Excel, and PowerPoint formatsLightweight suite that installs quickly and uses minimal system resourcesFamiliar user interface ensures a completely seamless migrationBuilt-in PDF editor and comprehensive toolkit available for free
microsoft office alternative - wps office

Frequently Asked Questions

What causes the 'Argument not optional' error in Access VBA?

This compile error occurs when a required parameter is missing in a VBA function or method call. In DoCmd methods, leaving a trailing comma or inserting an extra comma without providing the subsequent parameter will trigger this error.

How do I correctly reference a control on a subform in a query?

You must use the exact hierarchical path. For a standard subform, use: Forms!MainFormName!SubformName.Form.ControlName. The '.Form' segment is strictly required to access the controls inside the subform container.

Does being on a nested subform restrict the use of DoCmd.SendObject?

No, triggering code from a nested subform does not inherently restrict VBA commands like DoCmd.SendObject. As long as the command syntax is correct and the report's query properly references the nested form's controls, the email function will work normally.