• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 800
  • Last Modified:

Sub Report and Display Text on No Data


I have a subreport with a No Data event:
Private Sub Report_NoData(Cancel As Integer)

    MsgBox "No Data"
    Cancel = True

End Sub

The msgbox displays when I open the report separately but as expected the sub report msg box will not display when opening the Parent report and I do not want the msg box to display.  Instead, I want the subreport to display text "No Data" so that it could be seen on the printout.  

How can I do this?    thank you
  • 2
2 Solutions
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Add a label or text control on the parent that's normally hidden, then on the NoData event for the sub report, make that control visible.

Try this -

1.  Remove or comment out your existing subreport code

2.  Place a label in your MAIN report, immediately above the subreport, and call it lblNoData, captioning it something like "No Data In Subreport"

3.  Place this in the On Format event of your MAIN report's Detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.SubreportControlName.Report.HasData = False Then
    Me.SubreportControlName.Visible = False
    Me.lblNoData.Visible = True
End If
End Sub

Open in new window

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You can also do it another way, which is by setting a text control in the parent source to:

=IIF([SubReport].[Report].[HasData],Null,"NO DATA")

 I think that's a little less obvious though then doing it in the subreport.

pdvsaProject financeAuthor Commented:
thank you.  I put the code in the Footer Format event because this is where the subreport resides.   I split points because both said the same but mbizup made it a bit easier with the codes.
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.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now