Acces 2003: Declaring a Subreport in the page header

I have created a report that contains a page header/footer and a report footer.  The report footer is a subreport of similar information but is not "linked" to the main report (it does not need to be).  I need to change the page header once the report footer is printed.  If this is possible, what is the correct syntax so the report recognizes the sub-report and prints the correct (different) page heading.   The page header is different than all of the previous pages.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Well, there might be some trick to get the actual sub report page header to show, but if not ... you can fake one.
>I need to change the page header once the report footer is printed.

Are you sure?  Seems like there are no more page headers to print after the report footer.

You can use a report header on the first page and a page header on all the rest. The report has a page header property "not with report header" that permits you to suppress the page header on the first page.

DebbieHamataniAuthor Commented:
Yes -- I am sure.   There is a field in the page header that is dynamic for the 314 pages that it prints.  The last 3 pages or from the subreport that needs to be different.  

Having a reporting header is a great suggestion, but in my case will not work because the subreport prints three pages and I need a header on the those three pages.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
dqmq is correct.  It's basically too later. The Report Footer (w/subform) is going to be on the last page - at the bottom (of course) ... after the Report Page Header is printed.

Are you wanting 'page headers' in your subreport ?  If so, there is a trick for that.

Others may chime in and say I am wrong but...
You can't do things in the way your question is verbalized.
Reports have an order that things get done, and the page header is going to have it's Format Event before the report footer will.
And after a format event, there isn't a lot of change permitted.

The sub-report itself is also permitted to have a Page Header
You may want to experiment with that, as well

In general the order is
Page Header
Report Header
Any Group Headers
Any Group Footers
Report Footer
Page Footer

On any multi-page report, the Page header and page footer occur first and last, with anything else in between.

Any code that is going to change the Page header HAS to occur in the PageHeaderSection_Format event.
Attached is a sample.
Look at Report2
It uses a test where it pulls the same recordset as the report and then tests if the end of the recordset has been reached on the report
That may work for you

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"The sub-report itself is also permitted to have a Page Header"
But it will not show on the main form - withing the Sub Report.

The key is that the changes are self contained in the PageHeaderSection_Format event
As @mx says, you can't do it after!

So, it becomes a question of "What test can I devise that tells the page header to change?"
Also note that your wording is poor.
Technically AFTER the report footer has finished formatting there is nothing left to add execpt the very last page footer.

I technically answered
"I need to change the page header once the report footer begins printing."
DebbieHamataniAuthor Commented:
I am sorry if my wording is poor.  I will try to explain things the best I can with the resources and time that  I have.   Hopefully, I can verbalize my dilemna to you.  

I understand how the report is suppose to print and how the sections work.  I was merely asking what is the syntax to tell the "pageheader_format" section that I would like to have a different page header before the subreport in the Report Footer prints.  I understand that once the report footer prints I am unable to change anything.  

I did browse at your syntax and a recordset will not work in this case.  I just need to be able to change one field in the header to read something different than what iit s currently printing.  If you can help that would be great!

I read somewhere that subreports will print page headers and that was how the report was formated before I realized the page header was incorrect.  Thanks for the suggestion.  
The synatx is not tough.  It's figuring out some test that can be contained within PageHeaderSection_Format that will work that is tough...and that depends on your data.
Something comes to an end before the ReportFooter begins to be formatted.
Usually its that the data bound to the RecordSource came to its last record.

Is your report not bound to any records, then?
This is not the dilemma
<I understand that once the report footer prints I am unable to change anything>
Once any section formats, you can't change it.

In your case, you want to change the Page Header, which is the very first section on any given page.
That means you must devise code that will accomplish this change in that very section.
Nowhere else will work
DebbieHamataniAuthor Commented:
All I am asking for is a solution to my problem.  The subreport is an independent source of the main report.  I am just presenting the data in a different way by using a subreport.   Is there code that can be put into the pageheader_format section  recognizing that the report footer subreport so I can manipulate the page header?  If this is no easy, I will need to look at other options.  I use Experts Exchange to save me time and money if a solution is present.  Please let me know if this is not possible.

If you know of the trick, then just let me know.  Thanks for being helpful.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"If you know of the trick, then just let me know."
Are you trying to get the page header in the subform show ... on the main form?

You have seen the sample I posted.  What you need can be done.  YOU have said that using a recordset will not work for you.  You have not said WHY that won't work.  Doesn't your main report have records?  Some method to signal that the end of the main report has occurred and the the report footer is formatting must be coded.  Normally, that is when the last record in the detail section has been formatted.  You say that method will not work.  The only reason that won't work is if you are using an unbound report-- which would be highly unusual.  Please talk about why a recordset will not work as a test.  There will be other ways, but they are more involved, and there's no reason to go there if the recordset will work
DebbieHamataniAuthor Commented:
The solution to this problem was more complex than what I described.  The main report has the following sections: Report Header, Page Header, Sub Header, Sub Footer, Pager Footer and a Report Footer.  The report footer section contains a sub report (this was a subset of the original data from the main report but was necessary to "link" to the main report) that had a page header which by design could not be visible.  

The solution that I gave the points to was partially what led me into solving this problem.  One possible solution was to use a record set, but because of the complexity of the report I gave up on trying to get to this work.

The trick was to turn off the page header after the last record in the main data set and use a fake sub section in the sub report that repeats.
I am glad you got it solved.
<One possible solution was to use a record set, but because of the complexity of the report I gave up on trying to get to this work.>
Alright, but then how did you alter things
<after the last record in the main data set >?

That's what using a recordset would have done.
And the recordset would have been as simple as
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset, dbSeeChanges)

If the report was based on a parameter query it's only slightly more complex to do

Dim db as database
Dim rs As Recordset
Dim qdf as QueryDef

Set db = Currentdb
Set qdf =db.QueryDefs(Me.RecordSource)
qdf(0) = WhateverParameter
qdf(1)= SomeOtherParameter
'etc. etc.
Set rs = qdf.OpenRecordset( dbOpenDynaset, dbSeeChanges)

So I am a little mystified as to why that was too complex to do...
and how you did it now.

But I am glad you got it solved.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.