Subreport has no records to show - display message?

[RCheesley]

Hi all,

I've done a bit of searching but all the answers I am finding relate to a report, and not a sub report.

I've got a report set up which is working perfectly, it has several sub-reports running off their own queries and whatnot.

I've set the reports to can grow/shrink yes, which is great.

What I would like to be able to do, is if the sub reports have no data to return (for example if the record being queried has no entries in a particular table, which is quite likely) that rather than leave the heading and a blank section, it prints "No Records Found".

Is there any way to do this in VBA or some such? Note this is for a SUBREPORT linked via child/master on PatientID.

 

[maff811]

Hi, did you ever find a solution to this? I am having the same issue.

 

[Deleted member 147267]

Look at the "similar posts" at the bottom of any thread for suggestions. I think you will find your answer in one of them.

 

[isladogs]

If not, I suggest you add a label behind your subreport with the required message and set it hidden by default.
Then add code to count the number of records in the subreport,
If =0, hide the subreport and make the label visible

 

[Deleted member 147267]

I thought that one of them showing below here is one that I read with an answer. Apparently not, so here is a link to it (which I suppose could also be found by searching in this forum)

if subreport has no data, then show label only

Hi all, I have a report with an embedded subreport. When the linked field is null in the subreport ( but the info is in the main report) I want the subreport to show only a label that says "no data". So i will see the report with the summary data on the top and " no data" on the bottom. Does...

www.access-programmers.co.uk

Post 4.

 

[arnelgp]

or you can do the same using technique
in this report/subreport.

 

[maff811]

or you can do the same using technique
in this report/subreport.

Hi arnelgp, your solution is the one that I prefer in terms of the desired output. I am having some trouble understanding the query that sits behind it though. I'll keep looking at yours and see if I can replicate it in my DB.

Thanks to everyone else who offered suggestion.

 

[arnelgp]

it just "add" a dummy record to the query, using Union Query.

 

[June7]

I have a report with 3 subreports, not all will always have data. Subreport without data will not show anything in Print preview. So I have message that displays when there is no data. I use 3 labels and code behind main report.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.lblOU.Visible = Not Me.ctrOU.Report.HasData
Me.lblCO.Visible = Not Me.ctrCO.Report.HasData
Me.lblRC.Visible = Not Me.ctrRC.Report.HasData
End Sub

Format event only triggers for Print preview or direct to printer. An alternative is a textbox with expression
=IIf([HasData], "", "No data available.").

However, if there are some calculations within subreport, error will likely display in Report view.

 

[maff811]

it just "add" a dummy record to the query, using Union Query.

Ahhh... I see, so with that, you are asking the query to show the first record, which is the 'dummy' record you have added. Is that right?