Hiding an Empty Sub-report

[ophirw]

Hi,

I have a big summery report that is made of 5 queries. one main query for worker details & 4 queries for variable information related to each worker as sub-reports.
I cant merge all queries since I get a lot of duplicates in the report.

My goal is to hide (visible = false) sub-reports that have no results. the problem is that those sub-reports show one line of labels with no data.

I have tried using VBA code on the Sub-report onLoad event & on the parent reports OnOpen event. but it did not work.

I have set the Sub-reports visible control to NO & I tried to check if the text-box of that sub-report contains a value to change visible to True.

the code I have tried is:

If Not IsEmpty(reports![Parant report]![Sub-report].report.text-box) then
reports![Parant report]![Sub-report].report.visible = True
End If



I have tried with IsNull(), I have tried to hide & then show & the opposite, I have tried from the parant report & from the sub-report events. but could never get it to show when there is data & to hide when there is no data.

[June7]

Code must go in the main report Detail Format event. Refer to the subreport container control Visible property. I always give subform/report container name different from the form/report they hold, like ctrSubNameHere.

If Not IsNull(Me.ctrSubNameHere.Report.textbox) Then
Me.ctrSubNameHere.Visible = True
End If

or try the HasData property
Me.ctrSubNameHere.Visible = Me.ctrSubNameHere.Report.HasData

[ophirw]

Thanks June7 but it didn't work.

I'll go over a few things just to make sure I got it:
1. I am using Access 2010
2. In order to name the Sub-report's container on the report I select the Sub-report on the report & under Other.Name (in the property sheet) I enter my SubName.
3. I could not find any "Detail Format" event in the property sheet of the report. I have tried the "On Open" (nothing happens) & "On Load" (I get error "2455". you entered an expression that has an invalid reference to the property Form/Report) .

[June7]

OnFormat is an event of report sections. Select the Detail section bar on the report to see its properties.

[ophirw]

Tried that but it didn't work.
the Detail section of the report contains 4 different sub-reports. I want to hide only one of them when it has no info (it shows a label once). as far as I understand the Detail "On Format" event affects all 4 sub-reports.

Is there a way to hide only one sub-report while the others are still visible?

[June7]

That should be possible. Each subreport should be able to be referenced individually in the Format event.

You named and referenced the subreport containers as I suggested? Why didn't it work - what happened - error message, nothing? Do you want to provide project for analysis?

[ophirw]

I gave the sub-report a unique name, I have inserted the code with the relevant name in the "on format" event of the report's "detail" section.
When I ran the report there was no change on the report view. when I tried to push the export to PDF button I got a message saying the macro is preventing the rendering.

Without the code the sub-report's label shows when it is empty only on the report view, when exporting to PDF or printing, it does not show it if there is no data but there is a blank line instead.

How can i make the sub-report invisible in report view?
how can I make the sub-reports bellow it move up when there is no data in it & it doesn't show?

Attached is the project. the report I am talking about is "Accountant Report" & the sub report is "Absent Days" called "SubAbsentdays" in the reports detail section.
version 1 has no code
version 2 has the code in it

[June7]

Try code in the main report detail section Format event:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.SubAbsentdays.Visible = Me.SubAbsentdays.Report.HasData
End Sub

Couldn't test because I can't read the language on the form buttons so don't know which ones to push.

[ophirw]

That is exactly the code I have in the report detail format event.
Just to be sure I also copied the code you posted. the results are the same:
In the report view the sub-report appears whether it has data or not.
when i push the print or PDF button on the report i get the following message:
"A custom macro in the report has failed to run, and is preventing the report from rendering."

in order to run the report open the form "AccountantReportForm". In that form push the top button (out of the two on the form), the report will load.
after the report loads select the layout view, then in the property sheet select "detail". this will highlight all four sub-reports.In the detail area I am trying to make the 2nd sub-report invisible when there is no data.

attached is the project with the code in the format event.

[June7]

I did all that. The report runs without error. I had to add a label to the report so I could tell if the subreport displayed. The label you have on the subreport appears to be not associated with a data control. Associate it with one of the data controls and it will not display unless there is data. The code is not needed. However, there will still be a space where the subreport is positioned. Believe I encountered this issue before. My solution was to display a label on main report with a caption of "NO DATA" when no data returned by subreport. I set the label to Not Visible and then used the HasData code to set visible: Me.lblRC.Visible = Not Me.ctrRC.Report.HasData

You must delete the label and bring it back to get the 'Unassociated' message viewed by clicking the green triangle in corner when label is selected. Use Ctl+x, Ctl+v

[ophirw]

Ok, now I am really confused.

I tried all of that & nothing works (making the label invisible when there is no data in report view). I have cut the label "Days" in the "SubAbsentdays" sub-report in the "Accountant Report" main report & pasted it back & associating it with the txtbox "SumOfNumberOfDaysAbsent". This didn’t solve the problem even when I did that in the "Absent Days" report which is the original of the sub-report.

No matter what I did I just can't get it to be invisible in regular report view.

Attached is the project with the associated label (Days).

June 7 - If you managed to make the label disappear please upload the project to the thread so I can try it out & learn what you did & what I did wrong.

[June7]

Apparently the report is picky about which control the label is associated with. Associate it with the EmployeeID textbox. If you still have problems after that I will upload project.

[ophirw]

I am sorry, but even when I associate the label to EmployeeID txtbox I get the same behavior; when the EmployeeID visible property is set to Yes the label shows whether there is data or no, when it is set to no, the label doesnt show whether ther is data or not.

[June7]

I was just going to post a correction and I see your post. I messed up. Just realized the EmployeeID field is set as Not Visible and so the label never shows when associated with EmployeeID.

I went back and looked at my project that works. I see that there are no labels in the Detail section. They are all in headers and footers. So now I think that is the issue with your project.

So I tried something very new. Used the NoData event of the AbsentDays subreport.
Private Sub Report_NoData(Cancel As Integer)
Me.Days.Visible = False
End Sub

Unfortunately, that doesn't work either. The label never shows. Sorry, I don't have a solution that allows label in the Detail section. Best I could come up with is an unbound textbox with expression as ControlSource: =IIf(IsNull([EmployeeID]),"","ימי")

[ophirw]

Thanks for your effort. I also tried different events in different objects but you just cant get to a sub reports controls from a report. I also tried to do this on the original report that is impeded as a sub-report but to no success.
another approach I have tried was to make the whole sub-report invisible but that did not work as well.

I used in the VBA console to stop the code procedure in order to see in what object & event I can write the code to do this. the only object I could do that was the "Detail" object; only in that object you can refer to a single appearance of a sub-report without affecting the other appearances of that same sub-report. the problem was that the "On Format" event was never triggered & I could not find an event in the detail object that will trigger when the report is loaded.