We help IT Professionals succeed at work.

MS Access - Page N of M incorrect

laoits
laoits used Ask the Experts™
on
I have an Access report that runs on SharePoint tables.

The main report contains 3 'parent' subreports.  Each subreport contains between 5-8 of its own 'child' subreports.  I decided to nest subreports because I wanted a header and page break for each of the sections.  The user can check off which subreports they want to include from a form.

I don't know if this is relevant, but here is an example the code that calls the subreports.

In the 'parent' subreport:

Private Sub Report_Open(Cancel As Integer)
'Vital Statistics
    If Forms![frmDossier]![chkVitSta].Value = True Then
        Me.subVitSta.SourceObject = "rptSubVitSta"
    End If
End Sub

Open in new window


Private Sub gphVitSta_Format(Cancel As Integer, FormatCount As Integer)
    If Forms![frmDossier]![chkVitSta].Value = True Then
    If Me.subVitSta.Report.HasData = 0 Then
        Me.subVitSta.Height = 0
        Me.gphVitSta.Height = 0
    Else
        Me.gphVitSta.Visible = True
        Me.gphVitSta.ForceNewPage = 1
    End If
    End If
End Sub

Open in new window


The report works well, except for the page numbering.  As soon as you check off more than one subreport to include the highest page is always higher than the total number of pages (e.g. Page 30 of 29).  In this example, there are definitely 30 pages in the report and 29 is incorrect.

The page numbering is in the Page Footer of the main report.  It's coded to ignore the first page (a cover page):

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

Open in new window


I've tried the typical Page N of M code with the same result:
= "Page " & Page & " of  " & Pages 

Open in new window


If I run the report then print it to PDF the page number correct themselves in the PDF.  The same effect happens in the Print Preview of the report if you check then uncheck Print Data Only.

Is there a better way to do the page numbering or maybe a way to 'reprint' the report as happens with the PDF and Print Data Only?  Or is there something in my subreports I could change to stop this?

Thank you for any help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
This is always the issue with trying to control Page numbering with Subreports.

Not having access to the DB, makes this difficult for me to troubleshoot.
(what numbering on what reports...)

But as a Brute force test, try moving your code from the Format (event of the section(s)),  to the "Print" event.

Author

Commented:
Hi boaq2000,

I did as suggested and changed it from _Format to _Print throughout.  Nothing appeared but the cover page and a second blank page though.

To troubleshoot I made all the Sections Visible by default, and removed all the 'Height' statements (an error message said height cannot be modified on print...it seems like my Height statements might not be needed anyway).

Running the report like this with some or all subreports checked produced correct page numbering!

I was trying to prevent blank pages when a subreport contained no data, but even though every section is now visible, I'm not getting any blanks.  I guess I didn't need to worry about that just like height...

With everything as _Print instead of _Format there are 2 issues:

Each subreport doesn't necessarily start on a new page.  If there is room, it will start below the previous.  I don't think ForceNewPage can be processed on print.

Each subreport has a bit of code to add "continued" to the page header if it spans multiple pages.  It says "continued" in each page header now.  Here is my code for continued:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me.lblGrp.Caption = Me.Caption & " continued..."
End Sub

Open in new window

Do you have suggestions for these 2 issues?  Thank you for your help!  : )
MIS Liason
Most Valuable Expert 2012
Commented:
I typically don't "Nest" reports beyond two level deep.
(For many of the issue like the ones you are having)

To make everything simple, it is sometimes easier to make separate reports and not nest reports 3, 4, 5, ...ect., level deep.
Ex.: Instead of having one report for Customers-->Orders--->OrderDetails, (All Nested)
I would have a Customer/Orders Report and a separate Orders/OrderDetails Report.

Or create queries to bring in data from more that one table, then make one report.

Or a combination of both.
Create a query of Customers,Orders and OrderDetails and then create a "Grouped" Report.
This will display the same info as a three level nested report without all the machinations of getting the page numbers to work out just right.
Remember, most code to manage Report Page numbering is based on Reports being "Grouped", not nested
ex.: http://support.microsoft.com/kb/841779

So you might want to investigate some of these option...

Obviously, every situation is different, ...just my opinion.

;-)

JeffCoachman
Commented:
I can see how un-nesting could help!  For this report I think my best bet is the 3 layers of reports.

In playing around I learned that as long as you include "ForceNewPage" statements in the Open event of the report, the page numbering is correct.  If you remove or add more page breaks in the Format event of a Section the current page is always right, but the Total pages will be lower or higher than it should be.

ForceNewPage doesn't appear to work on the Print event of a Section.  Here's more on ForceNewPage:
http://msdn.microsoft.com/en-us/library/aa195895(v=office.11).aspx

I've ended up with a report with correct page numbering but I'm getting some blank pages.  If a user checks of all 'child' subreports in of a 'parent' subreport, but the last 'child' subreport contains no data there will be a blank page.  If I code the ForceNewPage into the Format event of the section using HasData (http://msdn.microsoft.com/en-us/library/bb213617(v=office.12).aspx), the total pages messes up.  

This is good enough for the report for now.  Thank you Jeff for all the information!

Author

Commented:
Accepted my last comment as part of the solution because it was another way to get correct page numbering.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
;-)