MS ACCESS REPORT PAGE TOTAL INCORRECT AFTER VBA FORMATS DETAILS SECTION

I have several reports in MS Access where the height of the DETAIL section changes depending on the data that is being displayed.

the report displays for example stock of clothing products - that go from XS,S,M,L,XL,2XL,3XL,4XL - but some garments come in Short, Reg, Long. in these cases the height of the details section increases so there are actually 3 rows of data displayed instead of 1. VBA handles this an it works perfectly.

the problem I am having is that the variable PAGES, holds the total number of pages in a report.

this is calculated by MS Access when the report loads - based on the number of rows of data in the Record Source.

when the DETAILS section is formatted - quite often it causes the report to run onto an extra page (possibly 2).

but the PAGES variable is not updated.

every page of the report that previews/prints needs to show PAGE X of Y - so I need to know the total number of pages when the first page of the report is rendered.

I am really not sure how to solve the issue

jack
LVL 1
jackbensonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Just verifying... is there a reason that you ruled out Access's built in report page numbering - (not working in this case)?
Capture.PNG
0
Dale FyeCommented:
In addition to Miriam's recommendation, I would ask, does this occur when you print the report, or when you use PrintPreview, or both?  

When you use PrintPreview, and the built-in page numbering, Access actually builds the report as it will be printed, and counts the number of pages, and then actually renders the report, so the numbers are correct.

Dale
0
John TsioumprisSoftware & Systems EngineerCommented:
Unless you provide a sample we can only speculate...
Usually in the Print event of the section you want you can put an unbound text box ...lets call it pageIndicator
Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
If Me.PrintCount = 1 Then
Me.pageIndicator = Me.Page
End If
End Sub

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
this is calculated by MS Access when the report loads - based on the number of rows of data in the Record Source.

  What you need to be aware of is that this is not the case.

  When you add the pages property to your report, you force the report engine into two-pass mode.    On the first pass it formats everything, but does not print.   On the second pass it prints the report.    This is the only way the pages can be accurate.

  The problem is that with the first pass, the OnPrint event does not fire.   If your carrying out any logic in that event it won't be executed on the first pass, but will on the second pass.

  So if you have code there,  you need to move it to OnFormat.

HTH,
Jim.
0
jackbensonAuthor Commented:
I am using the Access Built In Function : ="Page " & [Page] & " of " & [Pages]

this is showing the incorrect page numbering

Screen-Shot-05-15-18-at-11.38-AM.PNG
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
That is understood.

  The problem is that when you reference the [Pages] property, you force Access to run the report twice.    If you do anything in the OnPrint event that alters the page layout, then the [pages] will not be the same on the second pass.

  That's because on the first pass, Access only formats the report so it can get the total number of pages.   Then on the second pass it actually prints it.

  In some way, you are altering the layout of the report between the two passes.  That is the only way the pages property will be incorrect.  The other thing that could be happening is that your changing the value of the [page] property, but that doesn't sound like what is happening.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
In short, make sure you don't have any code in the OnPrint event.

 Also as John said, make sure you do it like this:

  If FormatCount = 1 then
      ' do your code here
  End If

 The reason for that is Access may execute the OnFormat multiple times for the section.  If your doing calculations there, it will throw them off if it execute the OnFormat more than once.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jackbensonAuthor Commented:
Jim,

if I move the code as follows then I get Page 1 of 0

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    PagesTB = "Page " & [Page] & " of " & Me.Pages
End Sub

is that what you wanted me to do?

many thanks

jack
0
jackbensonAuthor Commented:
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    If FormatCount = 1 Then
        PAgesTB = "Page " & [Page] & " of " & Me.Pages
    End If
End Sub

i still get Page 1 of 0
0
John TsioumprisSoftware & Systems EngineerCommented:
Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
 If PrintCount= 1 then
    PagesTB = "Page " & [Page] & " of " & Me.Pages
end if
End Sub

Open in new window

0
jackbensonAuthor Commented:
John,

I am getting Page 1 of 0 in preview and when printed when i just have the following code:

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
 If PrintCount= 1 then
    PagesTB = "Page " & [Page] & " of " & Me.Pages
end if
End Sub

Open in new window


jack
0
John TsioumprisSoftware & Systems EngineerCommented:
have you enabled the print event...copy-paste won't do the trick...design view your Report --> PageFooterSection --> On Print Event Procedure
0
Dale FyeCommented:
Why have you not simply added the page # the way Miriam (mbizup) recommended in the first response to your question.  you do not need to do this in code.

Get rid of the code, open the report in design view, click the # button in the Design - Controls section of the ribbon.  Then select the option for "Page N of M", select the Bottom of the page option and then click OK.
0
jackbensonAuthor Commented:
Dale.

I have

I replied it is giving me Pages 3 of 2

jack
0
John TsioumprisSoftware & Systems EngineerCommented:
How many pages actually  is your report...
Also just for the ...
try this
Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
 If PrintCount= 1 then
    PagesTB = "Page " & Me.Page & " of " & Me.Pages
end if
End Sub

Open in new window

0
jackbensonAuthor Commented:
John,

I have tried your code. me.pages returns 0

in this case the report is 3 pages due to the formatting in the details section

jack
0
John TsioumprisSoftware & Systems EngineerCommented:
Something is missing in the whole story...i guess its time to upload a sample
0
Dale FyeCommented:
Please copy all of the code you have in the report and paste it here so we can see if we can determine what is causing the problem.  After pasting, make sure you select all of the code, then click the "CODE" button in the format line of the comment section, that will make it a bit easier to read.
0
jackbensonAuthor Commented:
Option Compare Database
Option Explicit

Dim SumPageTotalItems As Long   'Running Grand total.
Dim SumTotalItems As Long

Dim mScaleTypeDetails As Long
Dim mScaleHeadingNo As Long
Dim mPageCount As Integer

Dim mScaleTypeCounter As Integer
Dim dLeft As Integer
Dim eLeft As Integer
            
Dim dGap As Integer
Dim eGap As Integer
    
Dim eHeight As Integer
        
Dim maxSizesOnRow As Integer
        
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'----------------------------------------------------------------------------------
'May need to use PrintCount to control this code.
'Debug.Print "Format", FormatCount, Me.RecNoProdItem, SumPageTotalItems, Me.Detail.WillContinue

    DividerHorisontal.Top = 0
    
    Dim i As Integer
    
    If Me.PrintCount <> 1 Then Exit Sub
        
    If mScaleTypeDetails = Me.ScaleHeadingNoForm Then
        For i = 1 To 24
            If IsNull(Me.Controls(Format(i, "\D00")).value) Then
                Me.Controls(Format(i, "\E00")).Visible = False
            Else
                Me.Controls(Format(i, "\E00")).Visible = True
            End If
        Next i
    Else
    
        mScaleTypeDetails = Me.ScaleHeadingNoForm
                      
        Qty.Top = 40
        D00.Top = 40
        
        For i = 0 To 24
            Me.Controls(Format(i, "\E00")).Height = eHeight
            Me.Controls(Format(i, "\D00")).Visible = True
            Me.Controls(Format(i, "\E00")).Visible = True
        Next i
        
        For i = 1 To 19
            Me.Controls(Format(i, "\H00")).Visible = True
            Me.Controls("Top" & Format(i, "\H00")).Visible = True
        Next i
                
        Dim scaleType As ScaleTypes
        
        scaleType = InitialiseScaleTypes(Me.ScaleHeadingNoForm)
    
        If IsNull(D00.value) Then E00.Visible = False
        
        If scaleType.rows = 1 Then
                    
            For i = 1 To 8
                Me.Controls(Format(i, "\R00")).Visible = False
                Me.Controls(Format(i, "\R00")).Top = 0
            Next i
            
            If (scaleType.columns <= maxSizesOnRow) Then
                        
                For i = 1 To scaleType.columns
                                        
                    Me.Controls(Format(i, "\E00")).Visible = True
                    'Me.Controls(Format(i, "\E00")).Top = Me.D00.Top
                    
                    Me.Controls(Format(i, "\D00")).Top = 40
                    Me.Controls(Format(i, "\D00")).Left = dLeft + (dGap * (i - 1))
                    
                    Me.Controls(Format(i, "\E00")).Top = 40
                    Me.Controls(Format(i, "\E00")).Left = eLeft + (eGap * (i - 1))
                    
                    If IsNull(Me.Controls(Format(i, "\D00")).value) Then Me.Controls(Format(i, "\E00")).Visible = False
                Next i
                       
        
                For i = (scaleType.columns + 1) To 24
                    Me.Controls(Format(i, "\D00")).Visible = False
                    Me.Controls(Format(i, "\D00")).Top = Me.D00.Top
            
                    Me.Controls(Format(i, "\E00")).Visible = False
                    Me.Controls(Format(i, "\E00")).Top = Me.D00.Top
                Next i
                
                For i = (scaleType.columns + 1) To 19
                    Me.Controls(Format(i, "\H00")).Visible = False
                    Me.Controls("Top" & Format(i, "\H00")).Visible = False
                Next i
                
                Me.Detail.Height = Me.E00.Height
            
            Else '(scaleType.columns > maxSizesOnRow)
            
                For i = 1 To scaleType.columns
                                    
                    Me.Controls(Format(i, "\E00")).Visible = True
                    'Me.Controls(Format(i, "\E00")).Top = Me.D00.Top
                    
                    Me.Controls(Format(i, "\D00")).Top = 40
                    Me.Controls(Format(i, "\D00")).Left = dLeft + (D00.Width * (i - 1))
                    
                    Me.Controls(Format(i, "\E00")).Top = 40 + D00.Height
                    Me.Controls(Format(i, "\E00")).Left = dLeft + (D00.Width * (i - 1))
                    
                    If IsNull(Me.Controls(Format(i, "\D00")).value) Then Me.Controls(Format(i, "\E00")).Visible = False
                Next i
                       
        
                For i = (scaleType.columns + 1) To 24
                    Me.Controls(Format(i, "\D00")).Visible = False
                    Me.Controls(Format(i, "\D00")).Top = Me.D00.Top
            
                    Me.Controls(Format(i, "\E00")).Visible = False
                    Me.Controls(Format(i, "\E00")).Top = Me.D00.Top
                Next i
                
                For i = (scaleType.columns + 1) To 19
                    Me.Controls(Format(i, "\H00")).Visible = False
                    Me.Controls(Format(i, "\TopH00")).Visible = False
                Next i
                
                Me.Detail.Height = Me.E00.Height
            
            End If
        Else ' scaleType.rows <> 1
            Dim oScaleHeadingsRow As ScaleHeadings
        
            oScaleHeadingsRow = InitialiseScaleHeadingsObjectForReportRow(Me.ScaleHeadingNoForm)
        
            R01.Caption = oScaleHeadingsRow.H01
            R02.Caption = oScaleHeadingsRow.H02
            R03.Caption = oScaleHeadingsRow.H03
            R04.Caption = oScaleHeadingsRow.H04
            R05.Caption = oScaleHeadingsRow.H05
            R06.Caption = oScaleHeadingsRow.H06
            R07.Caption = oScaleHeadingsRow.H07
            R08.Caption = oScaleHeadingsRow.H08
                    
            Dim Row As Integer
            Dim Column As Integer
            
            Row = 1
                   
            i = 1
            
            If (scaleType.columns <= maxSizesOnRow) Then
    
                Do While Row <= scaleType.rows
                    'MsgBox "Row: " & Row
                    Column = 1
                    Do While i <= scaleType.columns * scaleType.rows
                        
                        Me.Controls(Format(i, "\E00")).Visible = True
                        'Me.Controls(Format(i, "\E00")).Top = Me.D00.Top
                        
                        Me.Controls(Format(i, "\D00")).Top = 40 + (eHeight * (Row - 1))
                        Me.Controls(Format(i, "\D00")).Left = dLeft + (dGap * (Column - 1))
                        
                        Me.Controls(Format(i, "\E00")).Top = 40 + (eHeight * (Row - 1))
                        Me.Controls(Format(i, "\E00")).Left = eLeft + (eGap * (Column - 1))
                        
                        If IsNull(Me.Controls(Format(i, "\D00")).value) Then Me.Controls(Format(i, "\E00")).Visible = False
                        
                        'MsgBox i & " " & Format(i, "\E00") & " T:" & Me.Controls(Format(i, "\D00")).Top & " L: " & Me.Controls(Format(i, "\D00")).Left
                        
                        i = i + scaleType.rows
                        Column = Column + 1
                    Loop
                                       
                    Do While i <= scaleType.columns * scaleType.rows
                        
                        Me.Controls(Format(i, "\D00")).Visible = False
                        Me.Controls(Format(i, "\D00")).Top = Me.D00.Top
               
                        Me.Controls(Format(i, "\E00")).Visible = False
                        Me.Controls(Format(i, "\E00")).Top = Me.D00.Top
                        
                        i = i + scaleType.rows
                    Loop
                    
                    Me.Controls(Format(Row, "\R00")).Visible = True
                    Me.Controls(Format(Row, "\R00")).Top = (eHeight * (Row - 1)) + 40
                    
                    Row = Row + 1
                    i = Row
                Loop
                
            Else '(scaleType.columns > maxSizesOnRow)
            
                Do While Row <= scaleType.rows
                    'MsgBox "Row: " & Row
                    Column = 1
                    Do While i <= scaleType.columns * scaleType.rows
                        
                        Me.Controls(Format(i, "\E00")).Visible = True
                        'Me.Controls(Format(i, "\E00")).Top = Me.D00.Top
                        
                        Me.Controls(Format(i, "\D00")).Top = 40 + 2 * (eHeight * (Row - 1))
                        Me.Controls(Format(i, "\D00")).Left = dLeft + (D00.Width * (Column - 1))
                        
                        Me.Controls(Format(i, "\E00")).Top = 40 + 2 * (eHeight * (Row - 1)) + eHeight
                        Me.Controls(Format(i, "\E00")).Left = dLeft + (D00.Width * (Column - 1))
                        
                        If IsNull(Me.Controls(Format(i, "\D00")).value) Then Me.Controls(Format(i, "\E00")).Visible = False
                        
                        'MsgBox i & " " & Format(i, "\E00") & " T:" & Me.Controls(Format(i, "\D00")).Top & " L: " & Me.Controls(Format(i, "\D00")).Left
                        
                        i = i + scaleType.rows
                        Column = Column + 1
                    Loop
            
                    Do While i <= scaleType.columns * scaleType.rows
                        
                        Me.Controls(Format(i, "\D00")).Visible = False
                        Me.Controls(Format(i, "\D00")).Top = Me.D00.Top
               
                        Me.Controls(Format(i, "\E00")).Visible = False
                        Me.Controls(Format(i, "\E00")).Top = Me.D00.Top
                        
                        i = i + scaleType.rows
                    Loop
                    
                    Me.Controls(Format(Row, "\R00")).Visible = True
                    Me.Controls(Format(Row, "\R00")).Top = 2 * (eHeight * (Row - 1)) + 40
                    
                    Row = Row + 1
                    i = Row
                Loop
            
            End If
        End If
    End If
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
'----------------------------------------------------------------------------------
'Debug.Print "PrintDetail", PrintCount, Me.RecNoProdItem, SumPageTotalItems, Me.Detail.WillContinue
    If Me.PrintCount <> 1 Then Exit Sub
    If Me.Detail.WillContinue = False Then
        SumPageTotalItems = Nz(SumPageTotalItems, 0) + Nz(Me.Qty, 0)
    End If
End Sub

Private Sub Detail_Retreat()
'---------------------------------------------------------------------
'Debug.Print "Retreat", "-", Me.RecNoProdItem, SumPageTotalItems, Me.Detail.WillContinue
End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    If FormatCount = 1 Then
        PAgesTB = "Page " & [Page] & " of " & Me.Pages
    End If
End Sub

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
'----------------------------------------------------------------------------------
'May need to use PrintCount to control this code.
'Debug.Print "PageFooterPrint", PrintCount, Me.RecNoProdItem, SumPageTotalItems, Me.Detail.WillContinue
    If Page = 1 Then rptLastPageAccumalatedItems = 0
    If PrintCount = 1 Then
        Me.txtPageTotalItems = SumPageTotalItems - rptLastPageAccumalatedItems
        rptLastPageAccumalatedItems = SumPageTotalItems
        Me.txtLastPageAccumalatedItems = rptLastPageAccumalatedItems
        'Me.txtTotalItemsOnPickingList = SumPageTotalItems
        mPageCount = mPageCount + 1
    End If
    

    
    Dim DetailsHeight As Double
    Dim DetailsHeightNeeded As Double
    
    'DetailsHeight = Me.PageHeaderSection.Height - Me.PageFooterSection.Height - Me.Detail.Height
        
    'DetailsHeightNeeded = (RowsSumTB.value * (Me.E00.Height + Me.E00.Top)) + Me.ReportFooter.Height + Me.ReportHeader.Height
    
    If Page = Me.Pages Then SumPageTotalItems = 0
    
    If Page < Me.Pages Then
        Me.txtLastPageAccumalatedItems.Visible = False
    Else
        Me.txtLastPageAccumalatedItems.Visible = True
    End If
End Sub

Private Sub Report_Load()
    Me.Caption = "Picking List." & CStr(Nz(Me.OurRefNoSP, 0))
End Sub

Private Sub Report_NoData(Cancel As Integer)
'----------------------------------------------------------------------------
    MsgBox "There is no available data for this report", vbExclamation, Me.Caption
    Cancel = True
End Sub

Private Sub Report_Open(Cancel As Integer)
'-----------------------------------------------------------
    rptLastPageAccumalatedItems = 0
    rptPagePositionY = 0
    SumPageTotalItems = 0
    
    mScaleTypeDetails = 0
    mScaleHeadingNo = 0
    
    maxSizesOnRow = 10
        
    eHeight = 375
        
    dGap = 1020
    eGap = 1020
                
    dLeft = 4946
    eLeft = 5480
    
    Dim db As Database
    Dim qd As QueryDef
    Dim strSQL As String

    strSQL = "exec dbo.Report_Data_Picking_List " & [Forms]![frmBB]![TransNo]
    
    Debug.Print strSQL
    
    Set db = CurrentDb()
    Set qd = db.QueryDefs("qry_PickingList")
    qd.SQL = strSQL
    qd.Close
    
    Me.RecordSource = "qry_PickingList"
        
End Sub

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
    'ScaleHeatingCountLbl.Caption = mScaleTypeCounter
End Sub

Private Sub sechdrScaleHeadingNoReport_Format(Cancel As Integer, FormatCount As Integer)
'---------------------------------------------------------------------------

    If mScaleHeadingNo = Me.ScaleHeadingNoForm Then Exit Sub
    
    Dim oScaleHeadings As ScaleHeadings
    
    oScaleHeadings = InitialiseScaleHeadingsObjectForReportColumn(Me.ScaleHeadingNoForm)
    
    H01.Caption = oScaleHeadings.H01
    H02.Caption = oScaleHeadings.H02
    H03.Caption = oScaleHeadings.H03
    H04.Caption = oScaleHeadings.H04
    H05.Caption = oScaleHeadings.H05
    H06.Caption = oScaleHeadings.H06
    H07.Caption = oScaleHeadings.H07
    H08.Caption = oScaleHeadings.H08
    H09.Caption = oScaleHeadings.H09
    H10.Caption = oScaleHeadings.H10
    H11.Caption = oScaleHeadings.H11
    H12.Caption = oScaleHeadings.H12
    H13.Caption = oScaleHeadings.H13
    H14.Caption = oScaleHeadings.H14
    H15.Caption = oScaleHeadings.H15
    H16.Caption = oScaleHeadings.H16
    H17.Caption = oScaleHeadings.H17
    H18.Caption = oScaleHeadings.H18
    H19.Caption = oScaleHeadings.H19

    TopH01.Caption = oScaleHeadings.TopH01
    TopH02.Caption = oScaleHeadings.TopH02
    TopH03.Caption = oScaleHeadings.TopH03
    TopH04.Caption = oScaleHeadings.TopH04
    TopH05.Caption = oScaleHeadings.TopH05
    TopH06.Caption = oScaleHeadings.TopH06
    TopH07.Caption = oScaleHeadings.TopH07
    TopH08.Caption = oScaleHeadings.TopH08
    TopH09.Caption = oScaleHeadings.TopH09
    TopH10.Caption = oScaleHeadings.TopH10
    TopH11.Caption = oScaleHeadings.TopH11
    TopH12.Caption = oScaleHeadings.TopH12
    TopH13.Caption = oScaleHeadings.TopH13
    TopH14.Caption = oScaleHeadings.TopH14
    TopH15.Caption = oScaleHeadings.TopH15
    TopH16.Caption = oScaleHeadings.TopH16
    TopH17.Caption = oScaleHeadings.TopH17
    TopH18.Caption = oScaleHeadings.TopH18
    TopH19.Caption = oScaleHeadings.TopH19
    
    mScaleHeadingNo = Me.ScaleHeadingNoForm
    
    Dim i As Integer
    
    Dim scaleType As ScaleTypes
        
    scaleType = InitialiseScaleTypes(Me.ScaleHeadingNoForm)
        
    If (scaleType.columns <= maxSizesOnRow) Then
    
        For i = 1 To scaleType.columns
            Me.Controls(Format(i, "\H00")).Left = dLeft + (dGap * (i - 1))
            Me.Controls("Top" & Format(i, "\H00")).Left = dLeft + (dGap * (i - 1))
        Next i
    Else
        For i = 1 To scaleType.columns
            Me.Controls(Format(i, "\H00")).Left = dLeft + (D00.Width * (i - 1))
            Me.Controls("Top" & Format(i, "\H00")).Left = dLeft + (D00.Width * (i - 1))
        Next i
    End If
    
End Sub

Private Sub sechdrScaleHeadingNoReport_Print(Cancel As Integer, PrintCount As Integer)
    mScaleTypeCounter = mScaleTypeCounter + 1
End Sub

Open in new window

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
First, make sure you have a control with a control source set to:

=Me.Page & " of  " & Me.Pages  

 do not set the control in code.  You can hide the control if you want, but it must be a control with the control source set.

Second, in your PageFooterSection_Print, you have this:


    If Page < Me.Pages Then
        Me.txtLastPageAccumalatedItems.Visible = False
    Else
        Me.txtLastPageAccumalatedItems.Visible = True
    End If

  This is what I was talking about.  Code like this cannot be in the OnPrint.    Anything you do in the OnPrint event that alters the layout of the report could cause a difference in the page count.

   All code that alters the report layout (or has the potential to do so), must be in the OnFormat events.

   I haven't looked at all your code and there may be other places where you are doing this.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
One other minor point on this:

 If Page < Me.Pages Then

it should be:

If Me.Page < Me.Pages then

   always be as explicit as possible.  Never leave it up to Access to decide what it is your referring to.   And this is not only true with Access, but whenever your developing code.

  And one major point; Be aware that on the first pass, Me.Pages is always zero, so this check will fail on the first pass.
0
jackbensonAuthor Commented:
I have removed PageFooterSection_Print and amend PageFooterSection_Format as follows

I have added a TextBox that has the code is ="Page " & [Page] & " of " & [Pages]

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    If FormatCount = 1 Then
        PAgesTB = "Page " & Me.Page & " of " & Me.Pages
    End If
    
    If Me.Page = 1 Then rptLastPageAccumalatedItems = 0
    If FormatCount = 1 Then
        Me.txtPageTotalItems = SumPageTotalItems - rptLastPageAccumalatedItems
        rptLastPageAccumalatedItems = SumPageTotalItems
        Me.txtLastPageAccumalatedItems = rptLastPageAccumalatedItems
        'Me.txtTotalItemsOnPickingList = SumPageTotalItems
        mPageCount = mPageCount + 1
    End If
        
    Dim DetailsHeight As Double
    Dim DetailsHeightNeeded As Double
    
    'DetailsHeight = Me.PageHeaderSection.Height - Me.PageFooterSection.Height - Me.Detail.Height
        
    'DetailsHeightNeeded = (RowsSumTB.value * (Me.E00.Height + Me.E00.Top)) + Me.ReportFooter.Height + Me.ReportHeader.Height
    
    If Me.Page = Me.Pages Then SumPageTotalItems = 0
    
    If Me.Page < Me.Pages Then
        Me.txtLastPageAccumalatedItems.Visible = False
    Else
        Me.txtLastPageAccumalatedItems.Visible = True
    End If
End Sub

Open in new window


the same problem is still Page 3 of 2 (on both the new TextBox with the control source set to ="Page " & [Page] & " of " & [Pages] and the original control that is set by VBA
0
jackbensonAuthor Commented:
I have amended to test PageFooterSection_Format

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    If FormatCount = 1 Then
        PAgesTB = "Page " & Me.Page & " of " & Me.Pages
    Else
        MsgBox FormatCount
    End If

Open in new window


FormatCount is NEVER not 1 as the ELSE never triggers
0
jackbensonAuthor Commented:
i have commented out ALL the _Print sections and it now work :)

e.g. Detail_Print, ReportFooter_Print, sechdrScaleHeadingNoReport_Print
0
jackbensonAuthor Commented:

i have commented out ALL the _Print sections and it now work :)

e.g. Detail_Print, ReportFooter_Print, sechdrScaleHeadingNoReport_Print

I needed to add the Detail_Print back in - but it is still working fine
0
John TsioumprisSoftware & Systems EngineerCommented:
Just follow Jim's advise on a hidden textbox with ControlSource =[Pages] and you will be good to go...
0
hnasrCommented:
i have commented out ALL the _Print sections and it now work :)

No issue then?
0
hnasrCommented:
I needed to add the Detail_Print back in - but it is still working fine
Doe but indicate not working?

Try to recreate the issue using a simple database. This might land you on the solution. If not upload the sample database.
0
jackbensonAuthor Commented:
I got rid of the OnPrint events and I also have some PrintCount = 1 when it should have been FormatCount = 1

all working fine now

thanks for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.