Reset Page Numbering

[Terry Lo via AccessMonster.com]

I copied code from another of my reports that works into a new report. The page numbering is supposed to restart when the US01 field changes. On my new report is shows "Page 1 of 1" "Page 2 of 2" instead of restarting the numbering. Each should be only one page so each should say "Page 1 of 1". There will be cases where there are more than one page but for now I was trying it this way. The code below is in the page footer with corresponding text boxes. Any thoughts or suggestions?

Option Compare Database
Option Explicit

Dim DB As Database
Dim GrpPages As Recordset


Function GetGrpPages()
' Find the group name.
GrpPages.Seek "=", Me![US01]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If

End Function


Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![US01]
If Not GrpPages.NoMatch Then
' The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'First page of group, so add it.
GrpPages.AddNew
GrpPages![US01] = Me![US01]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub


Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"
End Sub

 

[Chris Reveille]

See
http://ourworld.compuserve.com/homepages/attac-cg/

http://www.mvps.org/access/reports/rpt0013.htm

Chris

-----Original Message-----
I copied code from another of my reports that works into a

new report. The page numbering is supposed to restart when
the US01 field changes. On my new report is shows "Page 1
of 1" "Page 2 of 2" instead of restarting the numbering.
Each should be only one page so each should say "Page 1 of
1". There will be cases where there are more than one page
but for now I was trying it this way. The code below is in
the page footer with corresponding text boxes. Any
thoughts or suggestions?

Option Compare Database
Option Explicit

Dim DB As Database
Dim GrpPages As Recordset


Function GetGrpPages()
' Find the group name.
GrpPages.Seek "=", Me![US01]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If

End Function


Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![US01]
If Not GrpPages.NoMatch Then
' The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'First page of group, so add it.
GrpPages.AddNew
GrpPages![US01] = Me![US01]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub


Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"
End Sub

 

[Anne]

I tried both and I can't get it to work.
Anne

See
http://ourworld.compuserve.com/homepages/attac-cg/

http://www.mvps.org/access/reports/rpt0013.htm

Chris

-----Original Message-----
I copied code from another of my reports that works into a

new report. The page numbering is supposed to restart when
the US01 field changes. On my new report is shows "Page 1
of 1" "Page 2 of 2" instead of restarting the numbering.
Each should be only one page so each should say "Page 1 of
1". There will be cases where there are more than one page
but for now I was trying it this way. The code below is in
the page footer with corresponding text boxes. Any
thoughts or suggestions?

Option Compare Database
Option Explicit

Dim DB As Database
Dim GrpPages As Recordset


Function GetGrpPages()
' Find the group name.
GrpPages.Seek "=", Me![US01]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If

End Function


Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![US01]
If Not GrpPages.NoMatch Then
' The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'First page of group, so add it.
GrpPages.AddNew
GrpPages![US01] = Me![US01]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub


Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"
End Sub

 

[Anne]

I just tried the one from the Access Web again and I got one step further
than before, but just like you, I am now getting Page 1 of 1, or 2 of 2 etc.
I have looked through the code over and over again, because I would really
like to get this working.
Is someone out there who can help?
Anne

Chris

-----Original Message-----
I copied code from another of my reports that works into a

new report. The page numbering is supposed to restart when
the US01 field changes. On my new report is shows "Page 1
of 1" "Page 2 of 2" instead of restarting the numbering.
Each should be only one page so each should say "Page 1 of
1". There will be cases where there are more than one page
but for now I was trying it this way. The code below is in
the page footer with corresponding text boxes. Any
thoughts or suggestions?

Option Compare Database
Option Explicit

Dim DB As Database
Dim GrpPages As Recordset


Function GetGrpPages()
' Find the group name.
GrpPages.Seek "=", Me![US01]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If

End Function


Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![US01]
If Not GrpPages.NoMatch Then
' The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'First page of group, so add it.
GrpPages.AddNew
GrpPages![US01] = Me![US01]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub


Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"
End Sub

 

[Anne]

Hi Terry, I found the answer with this terrific newgroup
http://www.utteraccess.com/forums/index.php
I put it to their reports forum and I ended up emailing a small database.
This code comes from:
http://www.mvps.org/access/reports/rpt0013.htm
Copy and paste the code and rename GrpNameCurrent=Me!YourControlname
Change the on format property of the page footer to event procedure.
Create a control in the page footer using: ="Page " & [Page] & " of " &
[Pages] and make it invisible, the controlname apparently does not matter.
Create a second visible unbound control and set the control name to:
ctlGrpPages
That is all there is to it. So simple. I would never have thought of the
invisible control of page of pages.
Anne

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----------replace this with the name of
your control
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

I copied code from another of my reports that works into a new report.

The page numbering is supposed to restart when the US01 field changes. On
my new report is shows "Page 1 of 1" "Page 2 of 2" instead of restarting the
numbering. Each should be only one page so each should say "Page 1 of 1".
There will be cases where there are more than one page but for now I was
trying it this way. The code below is in the page footer with corresponding
text boxes. Any thoughts or suggestions?

Option Compare Database
Option Explicit

Dim DB As Database
Dim GrpPages As Recordset


Function GetGrpPages()
' Find the group name.
GrpPages.Seek "=", Me![US01]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If

End Function


Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![US01]
If Not GrpPages.NoMatch Then
' The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'First page of group, so add it.
GrpPages.AddNew
GrpPages![US01] = Me![US01]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub


Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"
End Sub