Setting subreport visibility in Report

[enilc]

Hello all

I have an Access 365 report that contains 3 subreports. I would like to make one of the subreports visible, only if it contains data.

My plan was to use a control that contains a count of records in this subreport. If that count is zero, then set visibility of the subreport to false.

The subreport is called "NegConsentOutputAccounts_subreport"
The control with the record count is "text45" in the main report (placeholder while I work this out)

I thought I could get away with something as simple as:

Private Sub Report_Open(Cancel As Integer)
    If Me.Text45 = 0 Then
        Me.NegConsentOutputAnnuity_subreport.Visible = False
    Else
        Me.NegConsentOutputAnnuity_subreport.Visible = True
    End If
End Sub

But apparently not. Triggering the report yields no visible action. No error message. Removing/commenting the code and the report is generated as expected.

I verified that the text45 control is variable. I get the same non-result whether the value is zero or non-zero.

Thanks in advance for any assistance.

SC

[twinnyfo]

enilc,

Welcome to Bytes!

First, in order for such code to work, it would need to be placed in the OnFormat Event of the Report's section that contains the sub-report.

However, a much more easier way to do this is to have the height of the subreport set to 0, and have the subreport's CanGrow property set to yes. Thus, when there are records, the subreport will expand to show them; when there are no records, the report "disappears."

Please let me know if you have additional questions about implementing this.

Hope this hepps!

[NeoPa]

Hi.

I suspect that you're trying to use the value before it's been properly populated. The Open event triggers before the data is prepared. The Activate event, and be warned this can occur multiple times for the same report instance so code to run once needs to be wary of this, occurs after the Open event and may well be what you need. Give it a try maybe.

[NeoPa]

Also you can simplify the code somewhat to make it less clumsy :

Private Sub Report_Activate()
    With Me
        .NegConsentOutputAnnuity_subreport.Visible = (.Text45 <> 0)
    End With
End Sub

This won't be a problem if run multiple times so there's no real need to ensure it only runs once.

PS. Having now managed to see Twinny's suggestion I would reccomend trying that first. We both posted when the thread was still in the moderation queue so both our replies went there too. I've released all now.

If his approach works for you then it's even more elegant than having to write code. Why write code for something already handled perfectly well by Access naturally?

[enilc]

These responses have all been extremely helpful, both for this issue and some others.

However, after much trial and error I found something...quirky

I had the subreport set to grow/shrink. I had the subreport set to 0 height. And regardless the countless iterations of my previous code, the header and column headers would still appear on the master report, with no data.

I saw mentioned in several places that a subreport with no data should not appear on the master report, but that was not the case in this situation.

Finally, on a whim, I switched the report to "Print Preview" and the subreport was entirely not visible. I went back to "Report View" and the empty subreport was again, not visible.

So, what I found is that when a report is opened initially in report view, a subreport with zero records will appear in the report. It doesn't go to "not visible" until the view is changed to "Print Preview"

It's an odd functionality that I don't understand, but one I can live with.

@Twinnyfo: I hadn't seen the onformat event, but have other plans for that now. With regard to the second part of your post: as mentioned above, the subreport won't "disappear" (like I desire) until I go in-out of Print Preview for some reason.

@NeoPa: I've not used that format for code before, but I like the brevity and will use it in the future.

Thanks again for your quick and very helpful responses.
SC

[twinnyfo]

SC,

Also as a side note, concerning Report View (and layout View), I have all my reports default view set as "Print Preview" and I have the "Allow Report View" and "Allow Layout View" set to "No." This always ensures that when you are designing and testing your reports that "what you see is what you get." There can be some useful features of Layout View, but I prefer to make all my design and layout changes in Design view. I can control things a bit better there. Much of this all comes down to preference.

Let us know if you come across any other sticklers--we'll be glad to hepp!

[NeoPa]

As Twinny says, Print Preview is what you're interested in. I suggest you read up on what the others are provided for. It's not something you're likely to be interested in. Essentially Print Preview shows you what you would get were you to print it.

In my view anything else is pointless, but people have their weird and wonderful requirements. All you need to worry about is understanding what you actually want. That way you use Design View & Print Preview and the other options never get in your way.

PS. I echo Twinny's invitation to come back and post more questions when you need. We look forward to helping where we can.