Solved

Access Database Report Page Count not Working Correctly

Posted on 2013-06-17
8
476 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 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
 

Author Comment

by:rogerdjr
ID: 39258280
I thought it uploaded - here it is
CASpReports.accdb
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ms/access report does not open query 20 33
Copying and pasting pictures from Excel 2 22
Resize text 4 16
Square brackets 4 0
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now