none
Page Re-numbering on Report RRS feed

  • Question

  • Hello,

    I have a Access 2010 database called WIP on a Win7 pc. The database has a report named rptTrimsheet. This report contains a subreport named subrptTrimsheetInstr that is joined to rptTrimsheet by a field named txtPT_Num. I want rptTrimsheet to renumber the page numbers at each change in txtPT_Num.

    Any suggestions is appreciated.

    Thank you, Kevin

    Tuesday, March 19, 2019 11:24 AM

Answers

  • Hi Gustav. I found the following code on another post. It's above my skill level, but I'm willing to give it a try. It looks like it goes in the report On Format event. I will replace Salesperson with PT_Num. After I add a textbox to the report footer, what do I put into its Control Source? Thank you.
    Option Compare Database
    Option Explicit
    
    Dim GrpArrayPage(), GrpArrayPages()
    Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
    Dim GrpPage As Integer, GrpPages As Integer
    
    Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
    Dim i As Integer
      If Me.Pages = 0 Then    
        ReDim Preserve GrpArrayPage(Me.Page + 1)
        ReDim Preserve GrpArrayPages(Me.Page + 1)    
        GrpNameCurrent = Me!Salesperson
        If GrpNameCurrent = GrpNamePrevious Then
            GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1
            GrpPages = GrpArrayPage(Me.Page)
                For i = Me.Page - ((GrpPages) - 1) To Me.Page
                    GrpArrayPages(i) = GrpPages            
                Next i    
        Else
            GrpPage = 1        
            GrpArrayPage(Me.Page) = GrpPage
            GrpArrayPages(Me.Page) = GrpPage    
        End If
      Else
        Me!ctlGrpPages = "Group Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)
      End If
      GrpNamePrevious = GrpNameCurrent
    End Sub

    • Marked as answer by KevinATF Tuesday, March 19, 2019 5:47 PM
    Tuesday, March 19, 2019 5:34 PM
  • Nothing. It must be unbound.

    The code sets the value of the TextBox, here named ctlGrpPages:

    
    
    Me!ctlGrpPages.Value = "Group Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)


    Gustav Brock

    • Marked as answer by KevinATF Tuesday, March 19, 2019 5:47 PM
    Tuesday, March 19, 2019 5:41 PM
  • Thanks Gustav. Between your great advice and this article, I got it to work.

    https://bytes.com/topic/access/answers/530130-page-count-group

    Thank you for your help. Kevin

    • Marked as answer by KevinATF Tuesday, March 19, 2019 5:47 PM
    Tuesday, March 19, 2019 5:47 PM

All replies

  • How about a tiny piece of code in the OnFormat or OnPrint event of the detail section:

    Static LastCompanyID As Long
    
    If LastCompanyID <> Me!CompanyID.Value Then  
        ' New customer. Reset page counter.
        LastCompanyID = Me!CompanyID.Value  
        Me.Page = 1
    End If

    CompanyID would be your PT_Num.

    Gustav Brock


    Tuesday, March 19, 2019 1:49 PM
  • Gustav,

    Thanks you for your reply. I'm still new to VBA. I placed the code in the OnFormat event in the report Details section and replaced CompanyID with txtPT_Num. Next, do I need to add a textbox to my Page Footer section? If so, how to I call the VB in the textbox?

    Thank you, Kevin

    Tuesday, March 19, 2019 3:07 PM
  • Yes, you will need a textbox to display the page number having a ControlSource like:

    ="Page " & [Page] & " of " & [Pages] & ""


    Gustav Brock

    Tuesday, March 19, 2019 3:14 PM
  • Got it to work, but there's one issue. My report contains two PT_Num with 4 pages each totaling 8 pages. The Page # of # shows for example Page 1 of 8 instead of Page 1 of 4. How can I get the total number of pages for each PT_Num?

    Thanks for your help. Kevin

    Tuesday, March 19, 2019 3:33 PM
  • You are right. The total pages is not taken care of, and I can't see how to get that count in an easy way.


    Gustav Brock

    Tuesday, March 19, 2019 3:52 PM
  • Thanks for your help Gustav. Is there a non-easy way you can suggest to get this count?
    Tuesday, March 19, 2019 5:09 PM
  • It would be something like, during formatting, to count the pages for each num, store those counts in an array or collection, and then read these during printing.

    Gustav Brock

    Tuesday, March 19, 2019 5:21 PM
  • Hi Gustav. I found the following code on another post. It's above my skill level, but I'm willing to give it a try. It looks like it goes in the report On Format event. I will replace Salesperson with PT_Num. After I add a textbox to the report footer, what do I put into its Control Source? Thank you.
    Option Compare Database
    Option Explicit
    
    Dim GrpArrayPage(), GrpArrayPages()
    Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
    Dim GrpPage As Integer, GrpPages As Integer
    
    Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
    Dim i As Integer
      If Me.Pages = 0 Then    
        ReDim Preserve GrpArrayPage(Me.Page + 1)
        ReDim Preserve GrpArrayPages(Me.Page + 1)    
        GrpNameCurrent = Me!Salesperson
        If GrpNameCurrent = GrpNamePrevious Then
            GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1
            GrpPages = GrpArrayPage(Me.Page)
                For i = Me.Page - ((GrpPages) - 1) To Me.Page
                    GrpArrayPages(i) = GrpPages            
                Next i    
        Else
            GrpPage = 1        
            GrpArrayPage(Me.Page) = GrpPage
            GrpArrayPages(Me.Page) = GrpPage    
        End If
      Else
        Me!ctlGrpPages = "Group Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)
      End If
      GrpNamePrevious = GrpNameCurrent
    End Sub

    • Marked as answer by KevinATF Tuesday, March 19, 2019 5:47 PM
    Tuesday, March 19, 2019 5:34 PM
  • Nothing. It must be unbound.

    The code sets the value of the TextBox, here named ctlGrpPages:

    
    
    Me!ctlGrpPages.Value = "Group Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)


    Gustav Brock

    • Marked as answer by KevinATF Tuesday, March 19, 2019 5:47 PM
    Tuesday, March 19, 2019 5:41 PM
  • Thanks Gustav. Between your great advice and this article, I got it to work.

    https://bytes.com/topic/access/answers/530130-page-count-group

    Thank you for your help. Kevin

    • Marked as answer by KevinATF Tuesday, March 19, 2019 5:47 PM
    Tuesday, March 19, 2019 5:47 PM