Solved

Access Database Report Page Count not Working Correctly

Posted on 2013-06-17
8
536 Views
Last Modified: 2013-07-08
I have an access database report that includes several sub forms. Some of the sub forms are more than one page long – when I print, the total page count in the report footer does not count all the pages so I end-up with a footer that says something like “Page 150 of 145 on the last page.

Any idea how I can fix this?
0
Comment
Question by:rogerdjr
[X]
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
  • 4
  • 4
8 Comments
 
LVL 58
ID: 39256074
That's odd....are you trying to control the page count or is this the reports page and pages properties that actually are reading this?

Jim.
0
 

Author Comment

by:rogerdjr
ID: 39256813
It is the reports page count - I think it is because the subreport is more than one page long

Attached is a copy of the database

Report that is I am using is CASp_ReportOverallRpt04282013
0
 
LVL 58
ID: 39257033
No attachment was on your last comment.

Jim.
0
Industry Leaders: 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!

 

Author Comment

by:rogerdjr
ID: 39258280
I thought it uploaded - here it is
CASpReports.accdb
0
 
LVL 58
ID: 39262752
Roger,

 Sorry for the delay in getting back to you...I started on this yesterday and got side tracked.

 I would say with what your doing (including page breaks, requerying sub-reports, etc), it would be best to keep track of the pages yourself.

  I see you made an attempt to do this; did you have a problem with that or did you not complete it?

  I see you have code in the detail  print event to grab the page number, but nothing in the page footer to use that #.

  Ah wait, I see you did that for the table of contents sub report.

  So how to fix it; I would do the page numbering myself.   When you include the Pages property in a control, the report engine does the report twice.

  On the first pass, it figures out what the total pages will be, then does a second pass to actually print the report.  You can tell your on the first pass by checking pages, which will always be 0.

  I would use a hidden control with a running sum over the report as a key for saving the page number. Place it in the page footer.   As long as the layout does not change from pass 1 to 2, hat should square you away.

  I'd try it myself, but it's busy this morning and I wanted to make sure I got back to you with something before another day flies by.

  As an aide,  when you get into a complex report like this, your often far better off to generate all the required data for the report in temp tables and then base it off that so the data is static.

 Even so, I'm not sure why the report engine is messing up on the page numbers.

Jim.
0
 

Author Comment

by:rogerdjr
ID: 39269652
Created a button on the form that runs the report twice - works great but takes a while to run the report

I wonder if there is a way to control the DPI of acrobat from vba - sttting it to 72 dpi for the 1st pass and 600 dpi for the scond pass?

-----------------------------------------------------------------------------
Private Sub FullCASPReportPrintBtn_Click()
            Dim RptNm As String, RptNmlng As String, ReportPath As String, ReportId As String, RptMo As Double, RptDay As Double
           
            OverallPageCount = 0
            TableOfContentPagesUpdate = -1
           
            ReportPath = Me![RptOutputPath]
            ReportId = [Forms]![0_masterdatafrm]![HdrRptID] ' Me![ReportIdSel]
           
            RptMo = Month(Now())
            RptDay = Day(Now())
           
            ReportFilter = "ReportID = " & """" & ReportId & """"

            RptNm = "CASp_ReportOverallRpt04282013"
            RptNmlng = "CASp_ReportOverallRpt04282013"

            DoCmd.OutputTo acReport, RptNm, acFormatPDF, ReportPath & ReportId & " " & RptNmlng & " " & RptMo & "-" & RptDay & "-" & Year(Now) & ".pdf", , , , acExportQualityScreen

            TableOfContentPagesUpdate = 0

            RptNm = "CASp_ReportOverallRpt04282013"
            RptNmlng = "CASp_ReportOverallRpt04282013"
           
            DoCmd.OutputTo acReport, RptNm, acFormatPDF, ReportPath & ReportId & " " & RptNmlng & " " & RptMo & "-" & RptDay & "-" & Year(Now) & ".pdf", , , , acExportQualityScreen
           
            RptNm = "CASp_ReportOverallRptElementListSubRpt"
            RptNmlng = "CASp_ReportOverallRptElementListSubRpt"
           
            DoCmd.OutputTo acReport, RptNm, acFormatPDF, ReportPath & ReportId & " " & RptNmlng & " " & RptMo & "-" & RptDay & "-" & Year(Now) & ".pdf", , , , acExportQualityScreen


End Sub
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39271512
<<Created a button on the form that runs the report twice - works great but takes a while to run the report>>

 That's interesting; couldn't get it squared away in the report itself on the first pass?  If not and your building everything up externally with two prints of the report, and if your using your own page count, then remove the reference to the [pages] property in the report.

 That will pop it back into one pass mode for each print and speed things up.

<<I wonder if there is a way to control the DPI of acrobat from vba - sttting it to 72 dpi for the 1st pass and 600 dpi for the scond pass?>>

 No.  There is no programatic control over the PDF plugin in Office.

Jim.
0
 

Author Closing Comment

by:rogerdjr
ID: 39308525
Thanks
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

724 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