How to reset the page number and the total page count for each group in a Microsoft Access report and keep counting all the pages from 1 to the end

How to reset the page number and the total page count for each group in a Microsoft Access report and keep  counting all the pages from 1 to the end for example the group 1 will be
1  5  1
2  5  2
3  5  3
4  5  4
5  5  5  then the second group will be
1  4  5
2  4  6
3  4  7
4  4   8           the  last digit should show all the pages in sequence
im using theis link
http://support.microsoft.com/default.aspx?scid=kb;en-us;209215&Product=acc2000
but the problem the page number will be reset to 1 in every gorup how can get the last digit for all pages  in sequesce
osama120Asked:
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You want to follow the instructions in this MSKB article:
http://support.microsoft.com/kb/841779
Which uses the two pass method.  First pass is used to record the total pages in each group, second pass actually prints the report with your saved values.
Note: Make sure you put any code that should be executing on the first pass (to record the page number) in the onformat events.  Onprint does not fire on the first pass by the report engine.
If you get stuck on any of that just holler.
JimD.
0
osama120Author Commented:
this the code i used i try  every thing to record the page number but still it will rest  to 1 in evry starting group  fro example the first group will be
1 3 1
2 3  2
3  3  3
second group:
1 4  1
2 4  2
3 4  3
4  4  4
i want the last digit to pages in sequence
like this
Option Compare Database
Option Explicit
Dim db As Database
Dim GrpPages As Recordset


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
  Page = 1
End Sub
Function GetGrpPages()
   'Find the group name.
   GrpPages.Seek "=", Me![SHADOW_ACCOUNT]
   If Not GrpPages.NoMatch Then
      GetGrpPages = GrpPages![Page Number]
   End If
End Function

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
 On Error GoTo Err_PageFooterSection_Format
 Dim Mydb As DAO.Database
 Dim OPic As Object
 Dim Serial As Object

 'Dim L_NEW, L_UPDT, L_FOUND As Integer
 'Dim QPP_N As String
'Find the group.
   GrpPages.Seek "=", Me![SHADOW_ACCOUNT]

   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
      'This is the first page of the group. Therefore, add it.
      GrpPages.AddNew
      GrpPages![SHADOW_ACCOUNT_NBR] = Me![SHADOW_ACCOUNT]
      GrpPages![Page Number] = Me.Page
      GrpPages.Update
   End If
   
   
 Set Mydb = CurrentDb
 Set OPic = Mydb.OpenRecordset("tblPic")
  
 
    If Not OPic.BOF Then OPic.MoveFirst
    
     If Me![PageNumber] <> Me![osama] Then
     Me.OLEBound138 = OPic![PIC]
     Else
     OPic.MoveLast
     Me.OLEBound138 = OPic![PIC]
     End If

     
   If Me![PageNumber] <> Me![osama] Then
   Me!lbAccountSummary.Visible = False
   Me!lbPreviousBal.Visible = False
   Me!OPENING_BALANCE.Visible = False
   Me![DECODE(OPENING_BALANCE_SIGN,'C].Visible = False
   Me!lbPurchase.Visible = False
   Me!DEPOSIT_BALANCE.Visible = False
   Me!PURCHASE.Visible = False
   Me!lbPayment.Visible = False
   Me!DEPOSIT_BALANCE.Visible = False
   Me!lbFinanceCharge.Visible = False
   Me!FINANCE_CHARGE.Visible = False
   Me!lbLateCharge.Visible = False
   Me!LATE_CHARGE_AMOUNT.Visible = False
   Me!lbNewBal.Visible = False
   Me!CLOSING_BALANCE.Visible = False
   Me![DECODE(CLOSING_BALANCE_SIGN,'C].Visible = False
   Me!Line69.Visible = False
   Me!lbPreviousPoint.Visible = False
   Me!OPENING_BALANCE_POINT.Visible = False
   Me!lbPointGained.Visible = False
   Me!POINT_GAINED.Visible = False
   Me!lbPointRedeem.Visible = False
   Me!POINT_REDEMPTION.Visible = False
   Me!lbNewPoint.Visible = False
   Me!CLOSING_BALANCE_POINT.Visible = False
   Me!TEXT_1.Visible = False
   Me!TEXT_2.Visible = False
   Me!lbQR1.Visible = False
   Me!lbQR2.Visible = False
   Me!lbQR3.Visible = False
   Me!lbQR4.Visible = False
   Me!lbQR5.Visible = False
    Me!lbQR6.Visible = False
   Me!lbPearl_Points_Summary.Visible = False
   Else
   Me!lbAccountSummary.Visible = True
   Me!lbPreviousBal.Visible = True
   Me!OPENING_BALANCE.Visible = True
   Me![DECODE(OPENING_BALANCE_SIGN,'C].Visible = True
   Me!lbPurchase.Visible = True
   Me!DEPOSIT_BALANCE.Visible = True
   Me!PURCHASE.Visible = True
   Me!lbPayment.Visible = True
   Me!DEPOSIT_BALANCE.Visible = True
   Me!lbFinanceCharge.Visible = True
   Me!FINANCE_CHARGE.Visible = True
   Me!lbLateCharge.Visible = True
   Me!LATE_CHARGE_AMOUNT.Visible = True
   Me!lbNewBal.Visible = True
   Me!CLOSING_BALANCE.Visible = True
   Me![DECODE(CLOSING_BALANCE_SIGN,'C].Visible = True
   Me!Line69.Visible = True
   Me!lbPreviousPoint.Visible = True
   Me!OPENING_BALANCE_POINT.Visible = True
   Me!lbPointGained.Visible = True
   Me!POINT_GAINED.Visible = True
   Me!lbPointRedeem.Visible = True
   Me!POINT_REDEMPTION.Visible = True
   Me!lbNewPoint.Visible = True
   Me!CLOSING_BALANCE_POINT.Visible = True
   Me!TEXT_1.Visible = True
   Me!TEXT_2.Visible = True
   Me!lbQR1.Visible = True
   Me!lbQR2.Visible = True
   Me!lbQR3.Visible = True
   Me!lbQR4.Visible = True
   Me!lbQR5.Visible = True
   Me!lbQR6.Visible = True
   Me!lbPearl_Points_Summary.Visible = True
   End If
Exit_PageFooterSection_Format:
    Exit Sub

Err_PageFooterSection_Format:
    MsgBox Err.Description
    Resume Exit_PageFooterSection_Format

End Sub
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
Set db = DBEngine.Workspaces(0).Databases(0)
   DoCmd.SetWarnings False
   DoCmd.RunSQL "Delete * From [Category Group Pages];"
   DoCmd.RunSQL "Delete * From [tblSerialPages];"
   DoCmd.SetWarnings True
   Set GrpPages = db.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
   GrpPages.Index = "PrimaryKey"
   Set SerialPages = db.OpenRecordset("tblSerialPages", DB_OPEN_TABLE)
   SerialPages.Index = "PrimaryKey"
Exit_Report_Open:
    Exit Sub

Err_Report_Open:
    MsgBox Err.Description
    Resume Exit_Report_Open
End Sub

Open in new window

0
GRayLCommented:
I believe your counters should look like:

1  5  1
2  5  2
3  5  3
4  5  4
5  5  5
1  4  6
2  4  7
3  4  8
4  4  9

col1 is the group page no
col2 is the group id
col3 is the report page no

Agreed?
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!

osama120Author Commented:
yes
0
osama120Author Commented:
any soultion
0
GRayLCommented:
I think you got your answer at http:#a28884223
0
osama120Author Commented:
that link is the link i used to make the the group pages my problen the the page number will rest  to 1

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
  Page = 1
End Sub
if i try to get the pages in sequence it will not work i try declare a variable  in the
      Option Compare Database
       Option Explicit
       Dim db As Database
       Dim GrpPages As Recordset
       Dim intPage as long
i will and put the intPage as aconter in
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
intPage = intPage + 1
Me.Text197 = intPage
but when i run the report it will show the total pages not the pages in sequence


Option Compare Database
Option Explicit
Dim DB As Database
Dim GrpPages, SerialPages As Recordset
Dim intPage As Long
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
 
 Page = 1

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

 

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
 On Error GoTo Err_PageFooterSection_Format
 Dim Mydb As DAO.Database
 Dim OPic As Object
   
 'Dim L_NEW, L_UPDT, L_FOUND As Integer
 'Dim QPP_N As String
'Find the group.
   GrpPages.Seek "=", Me![SHADOW_ACCOUNT]
     
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
      'This is the first page of the group. Therefore, add it.
      GrpPages.AddNew
      GrpPages![SHADOW_ACCOUNT_NBR] = Me![SHADOW_ACCOUNT]
      GrpPages![Page Number] = Me.Page
      GrpPages.Update
      
   End If
 
  
  intPage = intPage + 1
  Me.Text197 = intPage
 
 Set Mydb = CurrentDb
 Set OPic = Mydb.OpenRecordset("tblPic")
 
 
  
 
    If Not OPic.BOF Then OPic.MoveFirst
    
     If Me![PageNumber] <> Me![osama] Then
     Me.OLEBound138 = OPic![PIC]
     Else
     OPic.MoveLast
     Me.OLEBound138 = OPic![PIC]
     End If
     
       
 
   If Me![PageNumber] <> Me![osama] Then
   Me!lbAccountSummary.Visible = False
   Me!lbPreviousBal.Visible = False
   Me!OPENING_BALANCE.Visible = False
   Me![DECODE(OPENING_BALANCE_SIGN,'C].Visible = False
   Me!lbPurchase.Visible = False
   Me!DEPOSIT_BALANCE.Visible = False
   Me!PURCHASE.Visible = False
   Me!lbPayment.Visible = False
   Me!DEPOSIT_BALANCE.Visible = False
   Me!lbFinanceCharge.Visible = False
   Me!FINANCE_CHARGE.Visible = False
   Me!lbLateCharge.Visible = False
   Me!LATE_CHARGE_AMOUNT.Visible = False
   Me!lbNewBal.Visible = False
   Me!CLOSING_BALANCE.Visible = False
   Me![DECODE(CLOSING_BALANCE_SIGN,'C].Visible = False
   Me!Line69.Visible = False
   Me!lbPreviousPoint.Visible = False
   Me!OPENING_BALANCE_POINT.Visible = False
   Me!lbPointGained.Visible = False
   Me!POINT_GAINED.Visible = False
   Me!lbPointRedeem.Visible = False
   Me!POINT_REDEMPTION.Visible = False
   Me!lbNewPoint.Visible = False
   Me!CLOSING_BALANCE_POINT.Visible = False
   Me!TEXT_1.Visible = False
   Me!TEXT_2.Visible = False
   Me!lbQR1.Visible = False
   Me!lbQR2.Visible = False
   Me!lbQR3.Visible = False
   Me!lbQR4.Visible = False
   Me!lbQR5.Visible = False
    Me!lbQR6.Visible = False
   Me!lbPearl_Points_Summary.Visible = False
   Else
   Me!lbAccountSummary.Visible = True
   Me!lbPreviousBal.Visible = True
   Me!OPENING_BALANCE.Visible = True
   Me![DECODE(OPENING_BALANCE_SIGN,'C].Visible = True
   Me!lbPurchase.Visible = True
   Me!DEPOSIT_BALANCE.Visible = True
   Me!PURCHASE.Visible = True
   Me!lbPayment.Visible = True
   Me!DEPOSIT_BALANCE.Visible = True
   Me!lbFinanceCharge.Visible = True
   Me!FINANCE_CHARGE.Visible = True
   Me!lbLateCharge.Visible = True
   Me!LATE_CHARGE_AMOUNT.Visible = True
   Me!lbNewBal.Visible = True
   Me!CLOSING_BALANCE.Visible = True
   Me![DECODE(CLOSING_BALANCE_SIGN,'C].Visible = True
   Me!Line69.Visible = True
   Me!lbPreviousPoint.Visible = True
   Me!OPENING_BALANCE_POINT.Visible = True
   Me!lbPointGained.Visible = True
   Me!POINT_GAINED.Visible = True
   Me!lbPointRedeem.Visible = True
   Me!POINT_REDEMPTION.Visible = True
   Me!lbNewPoint.Visible = True
   Me!CLOSING_BALANCE_POINT.Visible = True
   Me!TEXT_1.Visible = True
   Me!TEXT_2.Visible = True
   Me!lbQR1.Visible = True
   Me!lbQR2.Visible = True
   Me!lbQR3.Visible = True
   Me!lbQR4.Visible = True
   Me!lbQR5.Visible = True
   Me!lbQR6.Visible = True
   Me!lbPearl_Points_Summary.Visible = True
   End If
 
Exit_PageFooterSection_Format:
    Exit Sub

Err_PageFooterSection_Format:
    MsgBox Err.Description
    Resume Exit_PageFooterSection_Format

End Sub



Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
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"
 
Exit_Report_Open:
        Exit Sub

Err_Report_Open:
    MsgBox Err.Description
    Resume Exit_Report_Open
End Sub

Open in new window

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Note sure what your using this for:
  intPage = intPage + 1
  Me.Text197 = intPage
 
  but it's not needed.
Also, make sure your group level is based on  Me![SHADOW_ACCOUNT] and that the primary key index for Category Group Pages is SHADOW_ACCOUNT_NBR
JimD.

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
osama120Author Commented:
this link will shaow the right solution for my question
http://my.advisor.com/doc/17875?open&p=2&pid=ztdbms

 
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I don't know that I'd call it the "right" solution, but a solution.  It's the same basic technique as in the MSKB article link I've posted; that is your reference the pages property on the report, which forces the engine to do two passes.
 They were using an array rather then a table to store the pages and a little more code to figure out what page was what, but the basic technique is the same.
  I've used the code in the MSKB article myself for many, many years and it works as described.
  And now with my zone advisor hat on, a couple of comments that you may find helpful:

  In the future, if you find a solution on your own, you should accept your own comment as the solution (many are not aware that you can do that).  Also, if your having difficulty with a comment given, you should come back and say so and give a chance for an Expert to respond.  You'll get better results with EE and asking questions if you stay engaged in the question.  
 JimD.
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 Applications

From novice to tech pro — start learning today.