How to hide a label on 2nd subreport if 1st subreport has no data (1 Viewer)

[tsf.admin]

Hi,
Overview: Our non-profit has 2 separate mailings mid-year to almost the same recipients (about 600 recipients). I'd like to merge the 2 reports into 1 to streamline the process and save on postage. Recipients may get 1 report or the other or both or neither.

So far I have 3 queries and associated reports for each one

• 1st query is for the Main report that selects ALL the possible recipients and is the query for the Main report (outputs org info and the name/address of the recipient).
• 2nd query selects all the possible recipients for the 1st sub report (multiple or no records possible)
• 3rd query selects all the possible recipients for the 2nd sub report (multiple or no records possible)

I have been able to create most of what I need (by trial and error since I have not had any training with MS Access). The main report outputs all of the recipients for the Main query and report on a new page. If there is date for the 1st sub report but there aren't any records for the 2nd sub report, the report works like I want it to. But I have 2 problems:

1. When the first sub report does not have any data, I do not want the header label of the 2nd subreport to print out but I do want the detail records of the subreport to print. I have tried all kinds of ideas I have found on the internet, but have not been successful. I'm not very good with the events and procedures.
2. When there is no data for both the 1st or 2nd subreport, I don't want the Main report to print out at all. This is not too much of a concern because when we go to mail out the reports, if I come across one of these, I can just throw out the sheet. There shouldn't be too many with this issue.

I somehow need to make the informational label on the 2nd subreport invisible if there is no data on the 1st subreport but there is data in the 2nd sub report.

If there is any more information I can provide to help get an answer, please let me know. TIA !

 

[Gasman]

Look at the OnNoData event of the first sub report, and put code to hide the label in there.

Forms: Refer to Form and Subform properties and controls

 

[Ranman256]

on rpt2 load event, have it check the query count in rpt1

Private Sub Report_Load()
lblRpt2.visible =  Dcount("*","qsRpt1Query")>0
End Sub

 

[tsf.admin]

Hi,
I cannot get the label to disappear This is what I currently have.

Private Sub Report_Load()
[rpt-MidYear-balstmt-subreport].Report![NewsLabel].Visible = [rpt-MidYear-Awardee-List-subreport].Report.HasData
End Sub

Is there some other property on the label itself that needs to be changed?

 

[Gasman]

Did you write that yourself or use the Expression builder?
I would be using the Parent reference in there plus Me.
You also have to use the subfrom control name, NOT the subreport name. This is mentioned in the link I posted.

 

[CJ_London]

Perhaps use to code in the relevant sub report section on format event

 

[tsf.admin]

Did you write that yourself or use the Expression builder?
I would be using the Parent reference in there plus Me.
You also have to use the subfrom control name, NOT the subreport name. This is mentioned in the link I posted.

Thank you for the additional instructions.
Yes, I used the expression builder because I couldn't get the correct syntax on my own. I am too new to all this.

Is your link for (Forms: Refer to Form and Subform properties and controls) also apply to reports?
I will try again...

 

[Gasman]

Yes, the key is you have to use the subReport control name NOT the subReport name.
Same applied when referencing forms. Sorry, I can see that I mentioned subform (well subfrom actually )

I would expect you could use (untested)
Me.Parent.SubReportControlName.Report.ControlName.Visible = False

where that code is in the first subreport Amend the names to suit.

Look at that link I posted.

 

[tsf.admin]

Yes, the key is you have to use the subReport control name NOT the subReport name.
Same applied when referencing forms. Sorry, I can see that I mentioned subform (well subfrom actually )

I would expect you could use (untested)
Me.Parent.SubReportControlName.Report.ControlName.Visible = False

where that code is in the first subreport Amend the names to suit.

Look at that link I posted.

where do I find the subReport control name? I don't see a name other than the name of the subreport.

 

[Gasman]

Click the control border that houses the subreport.
They could be the same name. I always rename the control to something different, so I can tell them apart.

 

[tsf.admin]

After an unmentionable number of hours, I am very close.

I put this code in the ON-FORMAT event in the header section where the label resides:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
   Me.NewsLabel.Visible = Me.Parent![rpt-midyear-awardee-list-subreport].Report.HasData
End Sub

This hides the label I was trying to hide in the 2nd subreport when the 1st subreport does not have data. The 2nd subreport is not in the 1st subreport. Both reports are just in the Main report. Now to try to shrink the space where the label is hiding! I've got Yes to shrink and Grow in every place I could find it. I'm willing to live with this though.

Thank you for your ideas and patience with me as I worked on my first report with subreports.

 

[CJ_London]

Hiding won’t trigger grow:shrink, you need to reduce the height to 0