Subreport has no records to show - display message? (1 Viewer)

RCheesley

Registered User.
Local time
Today, 12:07
Joined
Aug 12, 2008
Messages
243
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

Registered User.
Local time
Today, 20:07
Joined
May 1, 2014
Messages
45
Hi, did you ever find a solution to this? I am having the same issue.
 

Micron

AWF VIP
Local time
Today, 08:07
Joined
Oct 20, 2018
Messages
3,476
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

MVP / VIP
Local time
Today, 12:07
Joined
Jan 14, 2017
Messages
18,186
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
 

Micron

AWF VIP
Local time
Today, 08:07
Joined
Oct 20, 2018
Messages
3,476
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)

Post 4.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:07
Joined
May 7, 2009
Messages
19,169
or you can do the same using technique
in this report/subreport.
 

Attachments

  • reportNoRecord.zip
    34.7 KB · Views: 97

maff811

Registered User.
Local time
Today, 20:07
Joined
May 1, 2014
Messages
45
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:07
Joined
May 7, 2009
Messages
19,169
it just "add" a dummy record to the query, using Union Query.
 

June7

AWF VIP
Local time
Today, 04:07
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:

maff811

Registered User.
Local time
Today, 20:07
Joined
May 1, 2014
Messages
45
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?
 

Users who are viewing this thread

Top Bottom