access subreports when data is null

Posted on 2012-08-22
Last Modified: 2012-08-23

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.

Question by:Evan Cutler
    LVL 26

    Accepted Solution

    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)
    LVL 77

    Assisted Solution

    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)
    LVL 9

    Author Closing Comment

    by:Evan Cutler
    Thanks Guys.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now