Reset page number of total pages in group

[roseyo]

I need my report to print total page numbers for groups of records, not the overall total pages for a report (i.e. "Page 1 of 5" for group 1, "Page 1 of 3" for group 2, etc. I used this code below that I found on the internet but can't get it to work. I replaced "Salesperson" w/my group "PositionName". In the foot header, I added an unbound text box and entered = "Group Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page). In the text box property, I entered the Name as ctlGrpPages. In the text box property for Source, I entered = "Group Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page). What am I doing wrong? Any help will be greatly appreciate. Thanks.
Roseyo

'************ Code Start *************
' This code was originally written by James H Brooks.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' James H Brooks



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!PositionName
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
'************ Code End *************
*** Note: the line Me!Salesperson should be changed to the control name of the group you wish to track pages for. Me!ctlGrpPages is the name of a control you should place in the page footer. You can also change this line to whatever form you wish your page numbers to take.

 

[speakers_86]

In the foot header, I added an unbound text box and entered = "Group Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page). In the text box property, I entered the Name as ctlGrpPages. In the text box property for Source, I entered = "Group Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page). What am I doing wrong? Any help will be greatly appreciate. Thanks.
Roseyo

You don't need to put that in the properties of the control. The only thing you need is to make sure the name of the control matches the name that is listed in the code: CtlGrpPages. The value is being set programatically.

 

[roseyo]

Thanks, Speakers_86, for your advice but I'm not sure what I'm not supposed to enter in the property of the control. Can you please take a look at the attached file and provide detail step what I need to do? I'm not too familar w/VB codes.

 

[speakers_86]

Clear the control source property.

 

[roseyo]

It doesn't work. When I cleared the text box control source property, the text box becomes unbound and no page numbering will show. I need it to print "Page 1 of 3 - Accounts Payable" and "Page 2 of 3 - Accounts Payable", etc.

 

[speakers_86]

Can you post your database?

 

[roseyo]

Thanks for your help! I greatly appreciate it.

 

[speakers_86]

For everyones' benefit, here is the link the op is using.

Roseyo, when you use code from online sources, please leave the copyright notices intact. It is also wise to put the url as a comment in the code too.

The reason you are struggling is because the this:

Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)

should be this:

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)

Also, I'm not sure why, but this would not fire for me.

  Else
    Me!ctlGrpPages = "Group Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)
  End If

It seems like it should, but it didn't feel like it I guess. I removed the 'Else', and moved the 'Me!' line below the 'End if'. I saw some success like that, but probably not perfect.

That should get you going.

edit-I emailed Dev Ashish about the missing 'Format'.

 

[roseyo]

I still couldn't get it to work even though I corrected the format, deleted Else, and moved the Me! line. Can you please correct the code in my file and send it back? I would like to run the report and see what you are getting because my page number has #Name?. Thanks.

 

[speakers_86]

Heres what I left off with. I may have changed a thing or two in trying to get it to work.

 

[roseyo]

Speaker 86...thanks for all your help. I see that it's working for the first 3-4 positions but then the rest of the group page numbering isn't working. I will play w/it to see if I can get it to work. It seems everyone who had tried this code, it's not working for them either. Wish I can get hold of the author email.

 

[speakers_86]

The reason that the Else..Me! was not working was because you are not making access compute the number of pages. Put a hidden text box somewhere and put =[Pages]. Then you can go back to the else...Me!...End if

 

[roseyo]

I did and it still doesn't work. At first page of #4 position name, it had Page 2 of 2 several times and then page 1 of 1 when the total pages should be 4. I may have to do w/o the total pages of each position name and use Page 1, Page 2, Page 3, etc. Frustrating!

 

[marmanjo]

This topic is a bit older, but the solution for that is to put the default "Page N of M" that exist in the report controls to force the report to make a second pass.

The author of the code said that : "Until access has finished formatting the report for the first pass, the value of the Pages property will be 0; so you can check this value to determine if this is the first or second pass. The first pass is explained below, and the second pass just sets the control in the page footer to the value in the arrays."

The problem with me was that second pass wasn't happening. Setting the default Page # to # forced it to pass a second time and correctly displaying the group pages. You then can set the default "Page N of M" to not visible.