Why is it so difficult to hide a sub report in a report when it has no data? I have seen a number of replies to simillar questions in this forum and they are all different and in my eyes don't have enough information to allow them to work. The nearest to a completion answer I have seen so far is the response by BobLarson in Jan 2001 "Hide subreport on no data".
Hide subreport on no data (1 Viewer)
- Thread starter 321jimk
- Start date
Thanks but still nothing@!?
Looking at your code it did dawn on me perhaps I should have placed my sub report in the group footer which I have now moved but I still can’t get any code to trigger on any event. I looked at “Tools/ References” in the VBA window to make sure I had ticked all the right 'references' and not missed something there but that seems OK. Think I’m going mad.
I’ll try and describe what I want the report to do just to avoid any confusion there: -
I am trying to show a list of invoices due for payment with the account number that each of them should be booked against. The reason I need a sub report is because some invoices have more than one item each of which should be booked against an account number. Obviously these could be for different accounts.
So the sub report should only ‘appear’ when there is data in a table where an invoice has been broken down into the separate elements. There is a link between the invoice ID and the individual elements stored in the separate table which is working fine.
So now I have placed the sub report in the group footer for “Invoices” so that any invoice where there is data in the related table should appear in the sub report. This happens fine but what I don't want to happen is for the space the sub report sit's in in the footer to be obvious when there is no data.
I must admit I do get a little confused referring to controls in other areas of the report i.e. sub reports and am not 100% sure sometimes how I should be calling these. Also I have not used VBA before with reports (I think) having mostly limited myself to Forms. Even here I still have trouble addressing controls in other sections of Forms especially subForms etc.
Any way not sure you’re going to be able to help me much as I’ll probably drive you mad as well.
Looking at your code it did dawn on me perhaps I should have placed my sub report in the group footer which I have now moved but I still can’t get any code to trigger on any event. I looked at “Tools/ References” in the VBA window to make sure I had ticked all the right 'references' and not missed something there but that seems OK. Think I’m going mad.
I’ll try and describe what I want the report to do just to avoid any confusion there: -
I am trying to show a list of invoices due for payment with the account number that each of them should be booked against. The reason I need a sub report is because some invoices have more than one item each of which should be booked against an account number. Obviously these could be for different accounts.
So the sub report should only ‘appear’ when there is data in a table where an invoice has been broken down into the separate elements. There is a link between the invoice ID and the individual elements stored in the separate table which is working fine.
So now I have placed the sub report in the group footer for “Invoices” so that any invoice where there is data in the related table should appear in the sub report. This happens fine but what I don't want to happen is for the space the sub report sit's in in the footer to be obvious when there is no data.
I must admit I do get a little confused referring to controls in other areas of the report i.e. sub reports and am not 100% sure sometimes how I should be calling these. Also I have not used VBA before with reports (I think) having mostly limited myself to Forms. Even here I still have trouble addressing controls in other sections of Forms especially subForms etc.
Any way not sure you’re going to be able to help me much as I’ll probably drive you mad as well.
GinaWhipp
AWF VIP
- Local time
- Today, 10:25
- Joined
- Jun 21, 2011
- Messages
- 5,900
Okay...
1. Where did you place the code?
2. Is this a Main report/Subreport scenario OR a Main report/Subreport/Subreport?
3. Please copy/paste the code as you have it now here.
4. Please provide name of Main report and Subreport.
5. Have you set the Subreport control to shrink? (And the section as well?)
1. Where did you place the code?
2. Is this a Main report/Subreport scenario OR a Main report/Subreport/Subreport?
3. Please copy/paste the code as you have it now here.
4. Please provide name of Main report and Subreport.
5. Have you set the Subreport control to shrink? (And the section as well?)
Gina,
Thanks.
Checking the database again today I noticed two essential lines of code missing ........
In answer to the questions you asked -
1. I created a group footer in the report for each record from the data source. I then adapted and posted your code in the On Format Event for this footer.
2. It's a main report/ sub report scenario. There are no sub sub reports.
3. This is the code in the group footer OnFormat event:
On Error Resume Next
____________________________________________________________
Note. I added other bits and pieces to the code as you can i.e. fields, labels etc to see if it would work with these rather than the whole sub report.
3. The name of the main report is: - rpt_Invoices2Pay
and the sub report: - rpt_InvoiceSubDetails
4. Yes to both.
Would it be of any help if I posted an extract from the database with the tables and report etc?
I have added two screen prints of the design of the report and report in view.
Once again thanks for your help.
Thanks.
Checking the database again today I noticed two essential lines of code missing ........
Option Compare Database
Option Explicit
I got home - and I added these - but it still didn't work!Option Explicit
In answer to the questions you asked -
1. I created a group footer in the report for each record from the data source. I then adapted and posted your code in the On Format Event for this footer.
2. It's a main report/ sub report scenario. There are no sub sub reports.
3. This is the code in the group footer OnFormat event:
Option Compare Database
Option Explicit
____________________________________________________
Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)Option Explicit
____________________________________________________
On Error Resume Next
If Me.rpt_InvoiceSubDetails.Report.HasData Then
End SubMe.rpt_InvoiceSubDetails.Visible = True
Me.GroupFooter0.Visible = True
Me.ServLine_Label.Visible = True
Me.ContractRef.Visible = True
Me.Report.Visible = True
ElseMe.GroupFooter0.Visible = True
Me.ServLine_Label.Visible = True
Me.ContractRef.Visible = True
Me.Report.Visible = True
Me.GroupFooter0.Visible = False
Me.Contractor.Visible = False
Me.rpt_InvoiceSubDetails.Visible = False
Me.ServLine_Label.Visible = False
Me.ContractRef.Visible = False
End IfMe.Contractor.Visible = False
Me.rpt_InvoiceSubDetails.Visible = False
Me.ServLine_Label.Visible = False
Me.ContractRef.Visible = False
____________________________________________________________
Note. I added other bits and pieces to the code as you can i.e. fields, labels etc to see if it would work with these rather than the whole sub report.
3. The name of the main report is: - rpt_Invoices2Pay
and the sub report: - rpt_InvoiceSubDetails
4. Yes to both.
Would it be of any help if I posted an extract from the database with the tables and report etc?
I have added two screen prints of the design of the report and report in view.
Once again thanks for your help.
Attachments
-
Report Design.jpg -
Report View.jpg
OMG!!
Didn't think you used that expression in the US - thought it was a typical british thing (Essex thing) !!
Anyway - felt really stupid when I read your post - how could I miss this?? However, tried to replicate the Print Preview view but couldn't!!
Changed the default view to Print Preview and/ or tried to to open it in Print Preview but got the following error message - (see attached).
The code stopped at: -
Sure this must be a tiny problem!?!?! HELP!!
Thanks for what you've done so far though.
Didn't think you used that expression in the US - thought it was a typical british thing (Essex thing) !!
Anyway - felt really stupid when I read your post - how could I miss this?? However, tried to replicate the Print Preview view but couldn't!!
Changed the default view to Print Preview and/ or tried to to open it in Print Preview but got the following error message - (see attached).
The code stopped at: -
If Me.rpt_InvoiceSubDetails.Report.HasData Then
In the Code Viewer now when I type: -
Me.
and 'look' for the name of the report e.g.
Me.rpt_InvoiceSubDetails
it 'can't find the name of report' whereas it could before (before what though!?!).
Sure this must be a tiny problem!?!?! HELP!!
Thanks for what you've done so far though.
Attachments
-
Error Image.jpg
No kiddin (that's US?)/ OMG (polite version) or Ooops..
(sorry - there is still a problem but have just had to leave for work - UK time. Will post further tonight)
Thanks again.
Named and shamed!
BUT......
(sorry - there is still a problem but have just had to leave for work - UK time. Will post further tonight)
Thanks again.
Last edited:
BUT NUTS...
It works!!!
Just got home and whatever happened (went wrong basically) when I tried it this morning - didn't happen tonight!!
How come it looks so flippin fantastically logical when it's right?
It's maddening!
Thanks again Gina.
It works!!!
Just got home and whatever happened (went wrong basically) when I tried it this morning - didn't happen tonight!!
How come it looks so flippin fantastically logical when it's right?
It's maddening!
Thanks again Gina.
Just been struggling with this myself, and trying to force it to open in PrintPreview so the empty subreports would actually hide, and it kept opening in ReportView.
Then it was facepalm moment when I read this post and realised I needed to change the code that runs the report to
DoCmd.OpenReport "Report Name", acViewPreview!
