Change Page header when subreport is in the report footer. 1

[Eddi Rae]

Hello,
I have a report that is using 3 columns.
Once the report is completed, I want the subreport; which is a normal full page report to print.

This is working correctly, but I want the page header to change when it gets into the subreport.
Is this possible?

Thanks for your help in advance!!
Eddi Rae

 

[dhookom]

Welcome to Tek-Tips. I hope you find all your questions answered here (about tech stuff).

Subreports don't have any Page sections so I assume you want the main report's page header. So the records in the main report have all printed followed by the subreport. I expect you can have code in the Page Header section to modify the caption of a label in the section.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Eddi Rae]

Correct. How would you go about changing the header when the Report footer is printing? That is what I am not sure about.

 

[dhookom]

I would need to test some things but the first idea I would try is add a a couple text boxes to 1) show a count of the number of records in the report 2) show a running sum over the report with a control source of =1. When these two text boxes have the same value, the different Report Footer should be displayed.

   Me.lblAlternativeFooter.Visible = Me.txtRunSumCounter = Me.txtRecordCount
   Me.lblOriginalFooter.Visible = Me.txtRunSumCounter <> Me.txtRecordCount

I'm not sure if this will work but I would start with a test.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Eddi Rae]

Sorry for my confusion.

It's not the Report Footer that needs to change. It is the Page Header. When I start printing the Report Footer; which is where my SubReport is located, I need the Page Header to change a field that will show that it is now printing the SubReport

The main report is a 3-column report. The Page Header should state that it is the "1st Article"
The Sub Report is a check-off list from the data that is on the main report. This is 1 column with the data. The Page Header should change to say "Inspection Sheet".

The field on the Page Header is a textbox.

I hope this explains a bit better that I did before.

 

[dhookom]

This is what worked for me and assumes some control names as used in the code. The checkoff sheet is all contained in the report footer with the section's Force New Page = Before Section. For testing purposed I left the text boxes visible and after confirming they work set them to Visible: No.

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    Me.lblAlternativePageHeader.Visible = Me.txtRunSumCounter = Me.txtRecordCount
    Me.lblOriginalPageHeader.Visible = Me.txtRunSumCounter <> Me.txtRecordCount
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Eddi Rae]

I have it almost working. Here is what I did:
I create two different labels and placed them on the Page Header.

When formatting the ReportHeader, I hide the alternate label and show the original label.
When formatting the ReportFooter, I hide the original label and show the alternate label.

My only issue now is that on the first page of the ReportFooter, it shows the original label and all other pages of the SubReport are showing the alternate label.
Do you know a way to remedy this?

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
    Me.lblOriginalLabel.Visible = True
    Me.lblAlternativeLabel.Visible = False
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    Me.lblOriginalLabel.Visible = False
    Me.lblAlternativeLabel.Visible = True
End Sub

 

[dhookom]

Did you try the running sum and count solution I proposed?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Eddi Rae]

No. How would I get the total records and the running sum to place into the text fields?

 

[dhookom]

Add both text boxes to the detail section of the main report.

Name: txtRecordCount
Control Source: =Count(*)

Name: txtRunSumCounter
Control Source: =1
Running Sum: Over All



Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Eddi Rae]

That works perfectly!!! Thank you so much!

 

[dhookom]

Glad to hear this is working for you.

If your question is answered successfully, please click the Great post! link to let others know the question has been completed.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016