For Each... Next - How do I reference subreports?

[DerekMcDonald]

Hi! I have a report with 4 unbound subreports in it. I'd like to be able to loop through the subreports to determine if they contain no data, and then display a label if they're all empty. Trouble is, while I can do this with forms, I can't figure out the code for reports. Here's kind of what I'm trying to do, with (?) in the area I'm stuck on:

Private Sub Report_Open(Cancel As Integer)

Dim rpt As Control
Dim intCounter As Integer

intCounter = 0

For Each rpt in me. ????? ("Controls" doesn't work...)
If rpt.Report.RecordsetClone.RecordCount > 0 Then
intCounter = intCounter + 1
End If
Next rpt

If intCounter = 0 Then
Me.lblCongrats.Visible = True

End If
End sub

Each subreport is bound to a separate query. Perhaps I'm approaching this all wrong? Any help would be appreciated!

 

[WildHare]

check out the HASDATA property..it will do what you want. I have a sub with a text box with this control source:

=IIf([TripsSub].[Report].[HasData]," ","There were no trips reported for this month&quot

So the text box is "blank" if there is data, and if there is no data, it prints the message.

The text box is placed just below the subreport control on my main report. Jim Hare
"Remember, you're unique - just like everyone else"
Feel free to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[DerekMcDonald]

Thanks! That almost works, but here's an interesting Access quirk. According to Microsoft, when a subreport has no data, it's unavailable. Therefore any line referencing the ctl.report.HasData (or any property) generates an error message.

In fact, I get this error message even when the subreports are populated. I think it has to do with the fact that the source object is Report.rpt_Alerts_AVLicSub without any linked fields. Access just isn't recognizing the reports as existing, so I don't know what to test for.

Perhaps I'll just bag the whole issue. Thanks, though!

 

[ottograham]

Derek:

I want to do something close to what you are doing. I have proposals to print which have 10-15 subreports included. Some of the subreports will not have data, and I want to delete them.

Would this approach work for you. If so, I'll let you know when I get it figured out.

Scott
grahamag@erols.com