Sub-Reports by Condition

[jamos]

I have a report which contains 5 sub-reports in the report header and 1 sub-report in the report footer. Each of the sub-reports is based on different data to the main report. I have a form where I select whether I want to run a report for, say, all states / counties or for a specific state / county which I choose from an automatically populated combo box containing only valid states / counties.

I have page numbers (Page x of y) on every page of the report except the first page (the title page showing which state / county I have selected for the report (or “All”)) and I programmatically ensure that new sub-reports and the main report detail section start on an odd page so that it publishes as you would for a book. Everything above works very well.

Now I have a problem I have been unable to find a solution to. If I run the report for all counties, I want all aspects of the report and sub-reports to run and show in the printout. Again, this works very well. However, if I run a selected county, I do not want the first 3 sub-reports to run or print even though they (would) contain data.

So far, I have been unable to find a solution. I am using Access 2003.

Is anyone able to assist or point me in the right direction please.

 

[MrQ]

Hi Jamos,
You need to place a check in the report header...
Something like....
Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
If Me.County = "All" Then
Me.SubReport1.Visible = True
Me.SubReport2.Visible = True
Me.SubReport3.Visible = True
Else
Me.SubReport1.Visible = False
Me.SubReport2.Visible = False
Me.SubReport3.Visible = False
End If
End Sub

Hope this helps

Q

 

[jamos]

Thank for the quick response MrQ.

I am only just starting with Access and am not sure how to link in the code to the report. The sub-reports I want to cancel (or skip or make invisible) are in the Main Report Header (i.e. prior to the Main Report Page Header section being activated). Would these be placed in the OnPage Event in the Main Report properties - if so, how do I enter them as I only seem to get a box allowing me to create new code not to link to the code you recommended which I put in a Utility Functions module. Is it possible to do it with a Macro - I don't see a Visible property option on the Report Properties All box.

Sorry for more questions, but I'm just a beginner.

 

[MrQ]

Jamos,
Don't fret my friend. We're all beginning at something.
Open your report in design view. Right click on the Report header and select properties. Go to the third tab in, labelled Event. You will see three lines, the top one being On Format. Drop this down and select [Event Procedure]. Now, click on the button to the right with the three dots in. This will open your visual basic editor. You'll see the following:

Option Compare Database

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Your code goes here...
End Sub


Place your code in where I have indicated above. That should work but if you've any issues with the code itself, just shout.

Hope that helps.

Also, sorry it took me a while to get back to you.

Q

 

[jamos]

Thanks MrQ.

I added the code as you indicated (code revised from your original as follows):

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
If [Forms]![Select Province or District for Report]![Report Option] = 1 Then
[Reports]![Warrants Issued by Century Sub-Report].Visible = True
[Reports]![Warrants Issued by Decade Sub-Report].Visible = True
[Reports]![Warrants Issued by Year Sub-Report].Visible = True
Else
[Reports]![Warrants Issued by Century Sub-Report].Visible = False
[Reports]![Warrants Issued by Decade Sub-Report].Visible = False
[Reports]![Warrants Issued by Year Sub-Report].Visible = False
End If
End Sub

When I run the report, the VB code terminates with run-time error '2451' advising the Report Name 'Warrants Issued by Century Sub-Report' you entered is misspelled or refers to a report that isn't open or doesn't exist'.

There is no misspelling and the sub-report does exist, so I can only assume it is because it is not open. However, if it IS opened, then isn't it too late to set the visible option ?

Have I misinterpreted your recommendation ?

Thanks again for the early response to my previous query.

 

[MrQ]

Hi Jamos,
The syntax you're using for referring to your subreports is slightly incorrect. Try removing the square brackets from around your [Forms].
Also, replace [Reports]![YourSubreportName].Visible with Me.[YourSubReportName].Visible.

Further to this, Google / search here for Refer to subreports in MSAccess.

Again, hope this all helps. Not got access in front of me at the moment but will check back when I have.

Q

 

[jamos]

Hi MrQ,

Firstly thank you again for your time and patience helping me with my concern.

I have amended the code in line with your advice, as follows:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
If Forms![Select Province or District for Report]![Report Option] = 1 Then
Me.Warrants_Issued_by_Century_Sub_Report.Visible = True
Me.Warrants_Issued_by_Decade_Sub_Report.Visible = True
Me.Warrants_Issued_by_Year_Sub_Report.Visible = True
Else
Me.Warrants_Issued_by_Century_Sub_Report.Visible = False
Me.Warrants_Issued_by_Decade_Sub_Report.Visible = False
Me.Warrants_Issued_by_Year_Sub_Report.Visible = False
End If
End Sub

This has the effect of producing the correct report when I select "All" (i.e. the True part of the above code) and it does turn the visibility off in the False part of the code. However, whilst it eliminates the display of the subreports it leaves blank pages where the reports would have been.

Again, I'm a little out of my depth here but I tried to set the Report Header CanShrink property to YES but this does not have any effect. I'm not sure what to do now as the subreports will contain data (albeit now hidden). Are you able to help with this one ?

Thanks in anticipation.

 

[MrQ]

Hi Jamos,
The only thing I can think is that you put the reports in a header of their own. Then, you would be able to set the visbility of the header itself which would solve the problem. If you're having any problems with this, if you could post a copy of your database (doesn't have to be a full version) I can have a look at it for you.

Hope again that this helps.

Q

 

[jamos]

Hi MrQ,

Problem resolved. I had not turned off the page breaks which were put in between the sub-reports and had also forgotten that the pagination macro was no longer available since it had been replaced by the Event procedure, so I have embedded that at the end of the code.

Thank you so much for your fast and rapid help in resolving my concern - you also taught me some new things along the way, for which I am most grateful.

For info, the code I ended up with reads as follows:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

If Forms![Select Province or District for Report]![Report Option] = 1 Then
Me.Warrants_Issued_Introduction_Sub_Report.Visible = True
Me.Warrants_Issued_by_Century_Sub_Report.Visible = True
Me.Warrants_Issued_by_Decade_Sub_Report.Visible = True
Me.Warrants_Issued_by_Year_Sub_Report.Visible = True
Me.PageBreak109.Visible = False
Me.PageBreak107.Visible = True
Me.PageBreak96.Visible = True
Me.PageBreak75.Visible = True
Me.PageBreak73.Visible = True
Me.PageBreak70.Visible = True
Else
Me.Warrants_Issued_Introduction_Sub_Report.Visible = False
Me.Warrants_Issued_by_Century_Sub_Report.Visible = False
Me.Warrants_Issued_by_Decade_Sub_Report.Visible = False
Me.Warrants_Issued_by_Year_Sub_Report.Visible = False
Me.PageBreak109.Visible = True
Me.PageBreak107.Visible = False
Me.PageBreak96.Visible = False
Me.PageBreak75.Visible = False
Me.PageBreak73.Visible = False
Me.PageBreak70.Visible = False
End If

If Page Mod 2 = 0 Then Me.PageBreak92.Visible = True Else Me.PageBreak92.Visible = False

End Sub

I will be revising the names of the page breaks to be more meaningful as well as adding a few comments to the code.

Thanks again for your help.

 

[MrQ]

Hi Jamos, Good for you my friend figuring it out. Well done.
Glad I could be of help.

Q