Hide an Empty Subreport and Its Label in Microsoft Access
Question details
Hide a subreport and its associated label when the subreport contains no records.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A parent report contains a subreport control and a separate label in the same section.
- Observed behavior
- When the subreport has no records, neither the subreport nor its label should appear in the formatted report.
Open the parent report in Design View and identify the names of the subreport control, its label, and the section that contains them. The subreport control name can differ from the name of its source report.
Use the parent section's Format event to hide both controls
Use the subreport's HasData property to control the visibility of both the subreport and its label.
In the Navigation Pane, right-click the parent report and select Design View.
Select the subreport control, open Property Sheet, and note its Name on the Other tab. Repeat for the label in the parent report.
Select the parent report section containing the subreport control. In Property Sheet, open the Event tab, choose On Format, select [Event Procedure], and click the ellipsis button.
Inside the generated event procedure, add: Dim hasRecords As Boolean hasRecords = Me.[NameOfSubreportControl].Report.HasData Me.[NameOfLabel].Visible = hasRecords Me.[NameOfSubreportControl].Visible = hasRecords Replace both bracketed names with the parent report's actual control names.
Save the report and open it in Print Preview. Check it with records present and with no subreport records to confirm that both controls appear or disappear as expected.
Create and share report documents with WPS Office
WPS Office is a free, lightweight alternative for editing and sharing documents and spreadsheets in a familiar interface. Open common Microsoft Office files to make migration easier. It does not replace Microsoft Access or run Access report VBA.
Frequently Asked Questions
Why does Can Shrink leave the label visible?
Can Shrink can reduce empty space, but it does not automatically hide a separate parent-report label. Use the containing section's On Format event to set the label's Visible property based on the subreport's HasData property.
Which subreport name should I use in the code?
Use the Name of the subreport control on the parent report, not necessarily the source report's name. Select the control in Design View and check Property Sheet > Other > Name.
Why doesn't the Format event work in Report View?
The section Format event runs when Access formats the report for printing or Print Preview. Open the report in Print Preview to test the event.
Can I hide only the label if the subreport already shrinks?
Yes. In the containing section's On Format event procedure, use Me.[NameOfLabel].Visible = Me.[NameOfSubreportControl].Report.HasData, replacing the bracketed names with your control names.
