Access Report – hide Subreport if no data

This Access tutorial explains how to hide Subreport if no data.

You may also want to read:

Access Report reset total page number and insert blank page

Access Report page break by Group and add blank page

Hide Access Subreport if no data

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

hide_subreport_00

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.

hide_subreport_03

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

hide_subreport_01

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

In the Master Report, select the Subreport, change the Height Property to 0 (Alternatively set Can Shrink Property to Yes).

Then change the Can Grow Property to Yes.

hide_subreport_04

Now if Subreport contains any data, the height is automatically expanded, otherwise Subreport will be invisible as below.

hide_subreport_02

 

VBA Solution to hide Subreport if no data

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

Insert the following code in an Event of Master Report.

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

Note that SubReportControl is the Control name of Subreport in Master report, not the actual Subreport name.

Outbound References

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

Leave a Reply

Your email address will not be published.