Access: Display Textbox Control In Sub-report when it has No Data

Question

In a subreport I created a sub on detail_format event that will display a text when there is no data returned.

‘Code in sub-report
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Report.HasData Then
    Me.Label43.Visible = True
    Me.txtNotEntered.Visible = False
Else
    Me.Label43.Visible = True
    Me.txtNotEntered.Visible = True
End If

End Sub

It works fine on the subreport when run alone. When I run the main report it doesn’t trigger.

I added the same code in the main report to see if it would work. It runs through the lines of code but still cannot see the txtNotEntered textbox control.

‘Code in main report
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me!rptResults_Comments.Report.HasData Then
    Me!rptResults_Comments.Report.Label43.Visible = True
    Me!rptResults_Comments.Report.txtNotEntered.Visible = False
Else
    Me!rptResults_Comments.Visible = True
    Me!rptResults_Comments.Report.Label43.Visible = True
    Me!rptResults_Comments.Report.txtNotEntered.Visible = True
End If

End sub

I am using MS Access 2003.

Answer 1

Since the subreport is bound to the main report, the subreport itself will not be shown if there is no data to connect it to the main report. You can see this better by setting the background color of the subreport's detail section to red, or any other non-white color.

One workaround is to move your txtNotEntered control to the main report and put it "under" the subreport control (using Send to Back). Then set your subreport control's Can Shrink property to True.

Then, when there is data in the subreport you will see the subreport and it will cover the txtNotEntered control. When there is no data, the subreport will shrink out of the way and you will be able to see the txtNotEntered control.

One advantage to this approach is that it requires no code. Just leave the txtNotEntered Visible property set to True. The shrinking of the subreport will take care of revealing it when appropriate.

Answer 2

In addition to the answer from mwolfe02, I would suggest instead of using a label and VB code use a textbox with the following expression:

=IIf([HasData],"","No data found")

I use this on all my reports. An expression like

=IIf([rptResults_Comments].[Report].[HasData],","No data found")

should work as well. I didn't test the latter expression, so I'm not sure if [Report] is needed or not.

Answer 3

In addition to @rick's problem, how to display "No data" text when a subreport has No Data, it is also common enough to want to handle the case where main report controls reference subreport controls (and the subreport(s) has(have) No Data). This might occur when you are calculating a total that references subtotals from many subreports.

So we can put together @mwolfe02's and @TheOtherTimDuncan solutions (repurposing from @TheOtherTimDuncan his use of the HasData property), with some incidental additions of my own, as a checklist.

We have a main report rptMain and a subreport srpSub. In rptMain, detail section, we have a subcontrol sbctSub which references srpSub.

1. On rptMain create a label (there's no general need for it to be a textbox) lblNoData with whatever text you like (e.g. "No Data Returned"). Place lblNoData under sbctSub (using Send to Back). Use the same Top value. This ensures these don't overlap vertically so problems don't occur when Shrinking/Growing occurs.

2. Add an "annotating" label that is always viewable by a developer in design view but is permanently set to Visible = No (which makes it invisible in non-design views) with text like "Hidden lblNoData under subcontrol". If this needs to shrink (Can Shrink = Yes) then make it an unbound textbox. Colour such an annotating label (or textbox) to stand out in design view (e.g. I generally use a blue). Otherwise, down the track, there's a danger of losing sight, as a developer, of what's going on (worse still if another developer has to analyze your work).

3. Ensure the following are set to Can Shrink = Yes:

   • rptMain, Detail.
   • sbctSub.

   When srpSub has no data then lblNoData will be revealed to the user as sbctSub will have shrunk to zero.

4. In production there might be issues with the No Data label showing through (white backgrounds seem to act transparently in some circumstances). If so, in rptMain:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
      Me.lblNoData.Visible = (Not Me.sbctSub.Report.HasData)
    End Sub
   

   (My "design spike" testing worked as @mwolfe02 mentioned. That is, no additional code was necessary and I could rely on the subcontrol to hide and reveal the No Data label as needed. However my production report had these weird transparency issues.)

5. If you have any control on rptMain (e.g. txtGrandTotal) that references a control on srpSub, then set the control source as follows

    =IIf([sbctTemp].[Report].[HasData],[sbctTemp].[Report]![txtSubTotal],0)
   

   Null to zero Nz() won't work where the referenced control doesn't even exist, due to the subreport returning no data.

Edit 01: Added additional code to hide and show the No Data label in case of transparency issues. Rearranged this from being listed the last step, to being listed more sensibly in the order.

Edit 02: Made mention that the annotating label should be a textbox if it needs to shrink.