Solved

Acces 2003: Declaring a Subreport in the page header

Posted on 2011-09-06
15
226 Views
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.
0
Comment
Question by:DebbieHamatani
  • 6
  • 4
  • 4
  • +1
15 Comments
 
LVL 42

Expert Comment

by:dqmq
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.


0
 

Author Comment

by:DebbieHamatani
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.
0
 
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.

mx
0
 
LVL 26

Expert Comment

by:Nick67
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
Detail
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


Mess-with-header.mdb
0
 
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.

mx
0
 
LVL 26

Expert Comment

by:Nick67
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."
0
 

Author Comment

by:DebbieHamatani
ID: 36492738
Nick67:
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!

DatabaseMX:
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.  
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 26

Expert Comment

by:Nick67
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?
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) 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.
0
 
LVL 26

Expert Comment

by:Nick67
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
0
 

Author Comment

by:DebbieHamatani
ID: 36493331
Nick67:
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.

DatabaseMX:
If you know of the trick, then just let me know.  Thanks for being helpful.
0
 
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?

mx
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36493461
Sigh.
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
0
 

Author Closing Comment

by:DebbieHamatani
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36560981
Ok,
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.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now