Solved

Access Database Report Page Count not Working Correctly

Posted on 2013-06-17
8
526 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 57
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 57
ID: 39257033
No attachment was on your last comment.

Jim.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:rogerdjr
ID: 39258280
I thought it uploaded - here it is
CASpReports.accdb
0
 
LVL 57
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 57

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.
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…

733 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