access subreports when data is null


I have a report, that contains multiple subreports.  I want the subreports to disappear when there is no data.  How do I accomplish this?

of note, inside the subreport there is also a count(*) textbox which will report "0" if no data exists.  I do not know if that makes a difference.

Evan CutlerVolunteer Chief Information OfficerAsked:
Who is Participating?
Alan WarrenApplications DeveloperCommented:
Hi arcee123,

Try adding this to the detail_format event of the main report

Open in new window

Obviously need to change the name of the sub report to your sub report names or containers if different from sub report.

Alan ";0)
For  the section of the main report that contains the subreport, use the section's format event procedure to test for data in the subreport...

If me.NameOfSubReportContainerControl.Report.HasData Then
     me.NameOfSubReportContainerControl.Visible = True
     meNameOfSubReportContainerControl.Visible = False
End if

Note that NameOfSubReportContainerControl is the name of the control on the main form that contains the subreport.  It is not (except by coincidence) the name of the subreport.

You can set the Can Shrink property of the main report section to Yes if you want to recover the space (and there is nothing else below the subreport in the section)
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
Thanks Guys.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.