Acces 2003: Declaring a Subreport in the page header

Posted on 2011-09-06
Last Modified: 2012-05-12
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.
Question by:DebbieHamatani
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +1
LVL 42

Expert Comment

ID: 36492537
>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.


Author Comment

ID: 36492562
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.
LVL 75
ID: 36492577
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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 26

Expert Comment

ID: 36492653
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

LVL 75
ID: 36492662
"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.

LVL 26

Expert Comment

ID: 36492674
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."

Author Comment

ID: 36492738
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.  
LVL 26

Expert Comment

ID: 36492811
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?
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 36492818
Well, there might be some trick to get the actual sub report page header to show, but if not ... you can fake one.
LVL 26

Expert Comment

ID: 36492827
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

Author Comment

ID: 36493331
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.
LVL 75
ID: 36493350
"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?

LVL 26

Expert Comment

ID: 36493461
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

Author Closing Comment

ID: 36557820
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.
LVL 26

Expert Comment

ID: 36560981
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.

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question