Including Footers on Subreports in Access

[time2hike]

My users have a database that they store the rules and requirements for how they do business in. For example: the Fiscal Policy or Board of Trustees Policy. The way the database reports are set up the user can print out the Title Page for the Fiscal Policy Manual and in a separate report the Table of Contents for the Fiscal Policy Manual and in a third report the Policies for the Fiscal Policy Manual. These 3 reports combined make up the Fiscal Policy Manual.

I am being asked to combine these 3 reports into 1 report. I can do this with 4th report that holds the 3 sub-reports. The issue is that each report has a different header and footer (in particular page numbering) and when I combine the reports I can not get the header or footer of the sub-reports to show. If I add a Header to the main report I am getting the header for the 1st sub-report (which then shows duplicative headers), but I can not get the header or footers for the other 2 sub-reports. Is there a way to force the header and footer to show or hide for each sub-report? Or is there a way to set page numbers to start on page 3 instead of page 1?

If you know of a better way to accomplish this and are willing to share with me I would really appreciate the help. I am stumped on this.

Thank you for reading my problem and sharing any solutions you have come across with me.

[dsatino]

Page or report level headers/footers?

[zmbd]

We will need the version of Access you are using.

IN V2010, I have a main report that combines two subreports and their headers are shown, (attached), this report was originally made in V2003.

The report headers in the subreports should print.
If the Header information is in the "Page Header" then these are suppressed by the main report. You will have to have either a macro, vba, or a control the refers to the subreport such as in (Allen Browns tip for bringing the totals to the main report

For the page of page... in the main report, page footer, insert text box, set control source to ="Page " & [Page] & " of " & [Pages]

So here's in design view (Sorry, had to cut the right side off due to privacy issues... just the pageofpage thing.)

Here's a cut and paste of the report. Once again, I had to redact some things due to privacy issues. Notice the "Averages", these are in the individual report headers.

Note the pageofpage is usually something like "1 of 20" the control for this is in the page footer of the main report as are the dates (pulled from the form used for the filter) shown at the bottom of the report.

[time2hike]

ZMBD, thank you for the good information. I appreciated your sample and understood what you were doing. And I liked the linked information about pulling the sum from a Sub-report to a main report. I did not know how to do this previously and I will add this to my tool box. However, I was reading the troubleshooting and number 1 thing to check is "Make sure the total text box is in the Report Footer section, not the Page Footer section."

My problem is that I need the page headers and Footers. Report headers don't repeat and each sub-report has a different page header that is needed on multiple pages. I can't put the page header in the main report because the needs of the sub-reports are different.

The issue with the Page of Pages in the report footer is that I only want them on the 3rd Sub-Report. My boss says it is unprofessional to have page number on the title page of the manual. Picky, picky!

I think this is probably a lost cause but maybe there is a way to get page headers and footers to appear when using sub-reports. If you have any other ideas that I should look into please let me know. I am using Access 2007 but if there is better tools in Access 2010 for this I could talk my company into upgrading to Access 2010.

Thank you very much for any information you can provide even if it is just a confirmation that what I need can not be done in Access.

[zmbd]

The equation can be modified such that:
=iif([page]<=1,"","Page " & [Page] & " of " & [Pages])

As for the page footer, why not use the same concept as AB's article and refer to the report control. We'll have to modify it a tad to account for the subreport names - I haven't done this before so it will take some time to work out.
The other option is to use VBA in the on-print event to transfer the subreport page footer information to the main report footer. Once again not something I've done before; thus, it may take some time to work out.

[time2hike]

zmbd, the page formula is on the right track. I am getting no numbers on the first page which is what I want. Do you know of a way to set the page numbers to start at page 1 on page 4? Example: If I hide page numbers for the first 3 pages instead of having page 4 show 4 can it show 1?

[zmbd]

Let's try a workaround for the header/footer.
I still think we can use VBA; however, I'm looking for simple here:
Open the subreport in design view - we will do this for each subreport. To avoid confusing Access, do not open the main report and work with only one subreport at a time.
The context Ribbon "Report Design Tools" will now be available.
Select the "Design" ribbon tab in the context tool ribbon (should be by default on 1st open in design view).
Click on "Group and Sort" in the Grouping and totals group.
This will show the Group, Sort, and Total window at the bottom of the report design window.
Select "Add a Group" - Now this is going to present a list of the fields in the report... ignor the fiels and look at the the bottom of the list, there will be the option "expresion" this is what we want, enter =1 for the first subreport (=2 for the second, =3 for the third - you can use the same =1 for all three; however, I'm setting up for another solution should this not work). The Group Header will be inserted automatically, you will need to click on the "More>" in the Group, Sort, and Total window for the group and then change the footer setting.
You can now go into the properties for the group header/footer and set what you need as for repeating etc...
Move everything in the page header/footer to this group header footer and then shrink your header/footer areas to nothing.

As for the Page number, we'll try some math, if you're going to supress the 1st three pages, so you now have "Page 4 of 200" in the footer, when you want "Page 1 of 200", then subtract 3.

These have all worked on my little test DB here at home.

[zmbd]

Here's what I have for you:

Thus the stuff in the =1 group repeats at the top of each page just like a header/footer.

>Although not shown, the =2 group is in the "55" subreport in the same manner as the "42"

[time2hike]

zmbd you have been a huge help! The Expression Group solved the Header issue and the report is starting to come together. Because reports 2 and 3 already have 2 groupings the footer expression appears at the bottom of the groupings not the bottom of the page, so that doesn't work as well as the header.

What I have come up with for the Footer on the main report is Formulas based on the pages. Example: The footer for all pages except page 1 need a motto statement so the formula is

=iif([Page]<=1,"","motto")

This works because I know there is only 1 page for the title that we don't want the motto on.
The page of pages I solved by adding the page numbers to the Table of Contents (page 2 and ...) and subtracting by 1

=IIf([Page]<=1,"","Page " & [Page]-1 & " of " & [Pages]-1)

Ideally there would be no page numbers on the table of contents. The problem is that for different manuals the Table of Contents can be more than one page so what works for one manual will not work for another. As I was explaining this I thought maybe I could add a field that stored the number of pages for the table of contents then use that field as the minus number in the pages to get the results they want. The problem is the users would not understand what to enter and why. Any ideas on how to resolve this last issue? Can Access determine how many pages exist on a sub-report?

[zmbd]

Which version of Access are you using?

[time2hike]

zmbd I am using Access 2007.

[zmbd]

I had tried to avoid using the VBA code for things; however, I think this may be where we need to go for the footers.
This is going to take some time to work out.

I need to look at the following example and the questions that follow so that I am sure where you are going with this:
[Section](pagecount)
[cover](1)
[TOC](2)
[SrA](3)
[SrB](6)
[SrC](2)
Total Page count is 14.

1) You want to suppress the page numbering for both [Cover] and [TOC].
2) Starting on the first page of [SrA] the footer should

2a) have "page 1 of 14" ?
2b) have "page 1 of 11." ?

3) In the footer you also need to have

3a) a different motto for each report?
3b) the same motto overall?

[time2hike]

Zmdb, See answers below.
1) Yes, I want to suppress the numbering for both [cover]and [TOC].
2b) Page numbering would start on [SrA] at page 1 of 11.
3b) The motto on the [TOC] and [Sr] is the same motto.

[zmbd]

OK<
I'm looking at a few things; however, it's my day off so I'm at home with the kids, don't get me wrong - but they take a lot more constaint interaction than the lab. :)

What I'm looking at are somethings from Microsoft's KB, Allen Brown's, and a few other techie sites.

• [-]Microsoft uses a table.
  [-]One site used an array.
  [-]Another site used the Custom Collection.
  [-]Differing page numbers by grouping (that "=1") thing :)
  [-]and there are a few other methods used.

However, none of the methods quite fit.

My thoughts are to attempt to use the fact that the forms and reports are essentially class-modules. As such, we can use a custom collection to hold some information about the report genereated during the formatting step. I can then either use this concept with the grouping thing or another idea.
It may slow your report creation a tad; however, I think it's a good solution. The method is however, not the easiest to poke and prod and I'm afraid you'll need to become quite comfortible with some VBA programming :).

Thus, I need to read thru the information a tad more - I sent my test database to the house so I have it to play with - inbetween the "no-its-mine!!!" and "would-u-quit-touching-me!!!!"

(thought I hit enter on this hours and hours ago!)

[time2hike]

ZMBD, I appreciate your assistance with this. Kids don't stay little for ever I think kids are more important than work. I wish I had realize that when mine were young. Thank you for helping me with this.

[zmbd]

OK,
I think I have this...

The underlying premis for this solution came from a website wherein the author used an array to store the current page information and the total pages. From what I understand, this is possible by forcing the report to process twice, once to get the page numbers and then a second time to get the total pages.
SO, if your report is HUGE, it will take a significant amount of time to process and return your report!!!

The only issue I have with this, is that arrays are limited to one data class type; thus, say if the user has a different text they want in the footer, then they're stuck looking for a way to solve that issue such as defining a user class type -yea :( . So, haveing ran across the table method I thought that's a right spiffy thing, and yet, if the motto thing again, do you make yet another field or table.

Hmm, then came the user custom collection. Can add stuff, can be different datatypes and you can implement in a dozen ways.

So, in the attached we have:
Two tables... these are my new template tables just for usual work. I'll be only using on.
Three Queiries, one for each subreport, filtering out on a table.
A form, it's just there as it's in my template.
Four reports, one rpt_main is the main report, and then SR1 - SR3.

I've set the main report up so that the Report Header has a simple graphic, and it will not have a page number or text at the footer.
I've then inserted, in the main report three null group levels (=1, =2,... ) as in Post#8 SLIGHT difference, no header or footer. I've set the properties so that a page break occurs before each section. I've then inserted the subreports in to the groups... SR1 under the "=1" etc...

In the Report footer we have three text controls.
One is hidden, to hold our offset for the page numbering.
The other two hold this construct:
=IIf(([Page]>[z_ctrl_txt_offset]),"TRUE","")
Where the "TRUE" will hold the value to be shown, either the motto or the "page N of M".

Keep in mind, we're going to suppress the line numbering on the title page, which will be taken from the main report header, and from SR1 which is in Group =1
The report has only four pages and we'll use that offset value to handle the suppresion and the page numbering.

Now because, OP has already banged head on rock and shown work ...

I hopw that the following code is obvious:

Option Compare Database
Option Explicit
'
'-------------------------------------------------------
'
'I'm using a collection here as I see some possible use for other option.
'making it public on a whim
Public z_col_page As New Collection
'
'-------------------------------------------------------
'
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
    'carry the offset to the hidden control
    If Me.Pages <> 0 Then
        Me.z_ctrl_txt_offset.Value = z_col_page!offset
    End If
End Sub
'
'-------------------------------------------------------
'
Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
'This goes in the group section on format event containing the report that we want to start page numbering from:
    With Me
        If .Pages = 0 And (FormatCount = 1) Then
            'need to subtract 1 for the offset as page numbers start with one
            .z_col_page.Add (.Page - 1), "offset"
            'let's say each report needed something else in the footer etc
            'then read the string from a control in the report
            .z_col_page.Add "the string", "the key"
        End If
    End With
End Sub
 

HOWEVER, just in case:
Line 8 I place the collection in the main (parent) report.
Line 15 is where I transfer out of the module to the form the value needed for the page offset.

Line 21: This is where the magic is!
This is in the on format event of the subreport in group "=2" (in the VBA collection, the groups start at 0 and count up,) Now, when this runs, the [Page] value will be calculated, so we know what page this should be (pg3) and we take 1 off, and store the value. At this point, we could also refer to the subreport controls or properties and pull a tag value or a motto value and store it too... it would take a tad more work however, to get different mottos for different reports. I think we'd have to move to the on-print event.

and Bob's your Uncle!

Please overlook any spelling and grammer issues, it's 06h00local here in the states and I've been up since 05h30 yesterday, with a slight snooze.
SO I'll zip this... and attch.
Please note, it is in V2010; however 2007 should be able to open the file.

[time2hike]

zmbd, Thank you for all your work on this. I really appreciate it! I opened the database you attached and followed the changes you have outlined above. I am getting this error message when I run this code on my database.
Microsoft Visual Basic
Run-time error '457':
This key is already associated with an element of this collection

When I step through your code in your database it steps as follows:

    If Me.Pages <> 0 Then
    End If
    With Me
         If .Pages = 0 Then
             .z_col_page.Add (.Page - 1), "offset"
            .z_col_page.Add "the string", "the key"
         End If
     End With
End Sub
    If Me.Pages <> 0 Then
         Me.z_ctrl_txt_offset.Value = z_col_page!Offset
    End If
End Sub
 

When I step through the same code on my database it gets to End With, End Sub, and then goes back to With Me instead of setting the Offset in the report. The error appears at the 1st .z_col_page line. Could this be because I have more pages in my report?

[zmbd]

I will have to go back to the test database and alter things a tad to use mulitiple pages; however, is the code you posted ( #17 ) the EXACT code you have in your database?

If is, I surprised it didn't do other errors, and compare lines 9 and 10 of your code against the lines 12 thru 32 of the code I posted ( #16 ).

[zmbd]

Hmmm... yes, appears that placing the code in the group header I've ran into the multipage issue, Sorry.

Let me take a look at what is going on in the test database. It may be something simple that I can do such as a flag etc.

[zmbd]

Fixed - Partially
Give me an Hour to (12h11CST now) to get things compressed and uploaded.

I will change this in the code posted in #16 .

Line 24 of the original code did not account for the formatting loop needed for the page count:

 If .Pages = 0 Then 

The change is quite simple, disallow the logic upon reentry, which is allready held in the "FormatCount"; thus the code becomes:

If .Pages = 0 And (FormatCount = 1) Then

I've made this change in #16 .

I need to fix the "Page 1 of M" where M is not being calculated correctly.


Once I compact the corrected database, I'll change that out too.

The alteration I made to the test database was to add some records to the tbl_data (note this is not related table, it's just some random type stuff to work with). I then added the tbl_data to the queries and pulled a field from that into the query table without any relationships. This results in the cartesian product --- creates a ton of records in a hurry :) !
I'll bump the thread when the new database is up.

[zmbd]

Simplified the control source in the "Page N of M" in the test database. It now reads:
=IIf(([Page]>[z_ctrl_txt_offset]),"Page " & ([Page]-[z_ctrl_txt_offset]) & " of " & (([Pages]-[z_ctrl_txt_offset])),"")

[zmbd]

Updated the database in Post #16

This has all of the changes incorporated in the code and the control.
I have also reordered the SR(#) grouping. The mock-page header was repeating within the detail section when the PK was chaning (1 to 2 to 3 etc...) The SR# group(=1):header was promoted to the top level and the general record sorting a grouping under "=1" in the subreport. Also set the SR# mock-page header to repeat by setting the repeat section setting for "=1" group to true.
All Rainbows and Sunshine!

[time2hike]

ZMBD, Thank you for the Rainbows and Sunshine! It is beautiful!!! Thank you so much for your help. I really appreciate you taking the time to work through this with me.

[zmbd]

My pleasure.

To be honest, I don't usually do the full blown database and code prefering instead to "help you help yourself" approach; however, reading the MVP forums, the MS sites, and other forums, the page numbering thing is a real task - even for the MS-MVPs!
Well we have it now, let's just hope your Boss doesn't want the "i - xi" type page numbering for the TOC, I might just cry ;,-(
Good luck with the rest of your project!