How to Hide an Empty Subreport in Microsoft Access
Question details
The user needs to hide a subreport in Microsoft Access and eliminate the resulting blank space when the subreport contains no records.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Formatting a parent report that contains a subreport which occasionally returns zero data records.
- Observed behavior
- The subreport continues to display its header and occupies blank space on the parent report even when it has no data to display.
Ensure your parent report and subreport are fully designed, and verify the exact name of the subreport control within the parent report's property sheet before applying VBA code.
Use the Format Event and HasData Property
This is the most reliable method to hide an empty subreport and reclaim blank space by using VBA in the parent report's section Format event.
By utilizing the Report.HasData property during the formatting stage of the parent section, you can dynamically hide the subreport control when no records are returned.
Right-click your parent report in the Navigation Pane and select 'Design View'.
Select the section containing the subreport (usually the Detail section). Open the Property Sheet, click the 'Event' tab, and click the ellipsis (...) next to the 'On Format' property.
In the VBA editor, enter code to check the HasData property. For example: If Me![YourSubreportControlName].Report.HasData = False Then Me![YourSubreportControlName].Visible = False Else Me![YourSubreportControlName].Visible = True End If.
Return to the Property Sheet. Select the subreport control and set its 'Can Shrink' property to 'Yes'. Then, select the parent section (e.g., Detail) and also set its 'Can Shrink' property to 'Yes'.
Check the RecordCount Property
An alternative method is to evaluate the subreport's RecordsetClone.RecordCount to determine if data exists.
Looking for a Free, Lightweight Office Suite?
While Microsoft Access is used for specialized database reporting, WPS Office provides a powerful, free alternative for all your everyday document, spreadsheet, and presentation needs. Experience seamless compatibility with Microsoft Word, Excel, and PowerPoint files.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package.
- 2. Install WPS Office: Run the installer to quickly set up Writer, Spreadsheet, and Presentation on your device.
- 3. Open Your Office Files: Seamlessly open and edit your existing Microsoft Office files without losing any formatting.
Frequently Asked Questions
Why does my hidden Access subreport still leave a large blank gap?
If a hidden subreport leaves a blank space, it usually means the 'Can Shrink' property is not enabled. Ensure 'Can Shrink' is set to 'Yes' for both the subreport control itself and the parent section containing it. Also, verify that there are no empty spaces between vertically stacked controls in the design view.
What causes error 2465 when trying to hide an Access subreport?
Error 2465 occurs when Microsoft Access cannot find the field or control referenced in your VBA code. This typically happens if you use the name of the underlying report instead of the name of the subreport control. Check the 'Name' property of the subreport control on the parent report and use that exact name in your code.
Can I hide a subreport using conditional formatting instead of VBA?
No. Conditional formatting in Access is primarily used for changing control colors, fonts, or enabling/disabling controls based on data values. To completely hide a subreport control and reclaim the physical space on the report, you must use VBA in the section's Format event.
