[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access Database Report Page Count not Working Correctly

Posted on 2013-06-17
8
Medium Priority
?
558 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
  • 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 2000 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

830 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