Access Report - hide Subreport if no data

This tutorial explains how to hide Subreport if no data on joining with Parent Report.

Hide Access Subreport if no data

Assume that you have a Master Report called Report A, the figures are displayed in Group Header.

Now you have a Subreport called B and you want to display data for each Group Header. Drag Report B to A and define the joined Fields.

As you insert B into A, the height of the section expanded significantly.

The problem it is that the actual height Group Header is expanded, because you have involuntarily adjusted the Group Header height.

Solution to hide Subreport if no data

The solution is tricky yet simple.

First, in Master Report, adjust the Subreport height to 0 (or set Can Shrink Property to Yes).

This will making everything in Subreport invisible.

Afterwards, in Master Report, set Can Grow Property to Yes. Therefore if there is data contained in Subreport, it automatically expands the Header height.

VBA Solution to hide Subreport if no data

You can also hide Subreport in VBA using Visible Property in Format E, but you still need to set the height of Subreport to 0 (or set Can Shrink Property to Yes).

Me.SubReportControl.Visible = Me.SubReportControl.Report.HasData

Note that SubReportControl is the name of subreport in main report, not the actual subreport name.

Outbound References

http://www.access-programmers.co.uk/forums/showthread.php?t=212478

Access Report - hide Subreport if no data