sramkris
asked on
MS Access Reports (Reset Page Number and total page count for each section of a report
Hi All,
I am trying to get the page number reset on change of a patient_id. I have a report which runs in to 2 to 3 pages per patient but there are cases where the pages are just one per patient. I followed the eg in the Microsoft knoledge base article http://support.microsoft.com/default.aspx?scid=kb;en-us;306127
I have followed the steps as per the document but the page number does not get set correctly. If i step through the code the number of pages in the grp is shown correctly but when it displays the report it is wrong. for eg if i have one page report it shows number of pages as 2..
Here is the code i have so far
Option Compare Database
Option Explicit
Dim GrpPages As Recordset
Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
' Set page number to 1 when a new group starts.
Page = 1
End Sub
Public Function GetGrpPages()
GrpPages.Seek "=", Me![Text27]
If Not GrpPages.NoMatch Then
' GetGrpPages = "Page " & Me.Page & " of " & GrpPages![PageNumber]
GetGrpPages = GrpPages![PageNumber]
End If
End Function
Private Sub PageFooterSection_Format(C ancel As Integer, FormatCount As Integer)
GetGrpPages
'GrpPages.Seek "=", Trim(Me!Text27)
If Not GrpPages.NoMatch Then
If GrpPages![PageNumber] < Me.Page Then
GrpPages.Edit
GrpPages![PageNumber] = Me.Page
GrpPages.Update
End If
Else
On Error GoTo ErrHandler
GrpPages.AddNew
GrpPages![Categoryname] = Me![Text27]
GrpPages![PageNumber] = Me.Page
GrpPages.Update
End If
GetGrpPages
ErrHandler:
If Err.Number <> 0 Then
MsgBox Err.Number & ": " & Err.description
Err.Clear
Exit Sub
End If
End Sub
Private Sub Report_Open(Cancel As Integer)
CurrentDb.Execute "delete * from [CategoryGroupPages];", 0
Set GrpPages = CurrentDb.OpenRecordset("C ategoryGro upPages")
GrpPages.Index = "primarykey"
End Sub
Category Group Pages is a table created in Access with Category Name and PageNumber as fields in the report with category name as PK.
Any tip to solve this issue is highly appreciated
Thanks
I am trying to get the page number reset on change of a patient_id. I have a report which runs in to 2 to 3 pages per patient but there are cases where the pages are just one per patient. I followed the eg in the Microsoft knoledge base article http://support.microsoft.com/default.aspx?scid=kb;en-us;306127
I have followed the steps as per the document but the page number does not get set correctly. If i step through the code the number of pages in the grp is shown correctly but when it displays the report it is wrong. for eg if i have one page report it shows number of pages as 2..
Here is the code i have so far
Option Compare Database
Option Explicit
Dim GrpPages As Recordset
Private Sub GroupHeader1_Format(Cancel
' Set page number to 1 when a new group starts.
Page = 1
End Sub
Public Function GetGrpPages()
GrpPages.Seek "=", Me![Text27]
If Not GrpPages.NoMatch Then
' GetGrpPages = "Page " & Me.Page & " of " & GrpPages![PageNumber]
GetGrpPages = GrpPages![PageNumber]
End If
End Function
Private Sub PageFooterSection_Format(C
GetGrpPages
'GrpPages.Seek "=", Trim(Me!Text27)
If Not GrpPages.NoMatch Then
If GrpPages![PageNumber] < Me.Page Then
GrpPages.Edit
GrpPages![PageNumber] = Me.Page
GrpPages.Update
End If
Else
On Error GoTo ErrHandler
GrpPages.AddNew
GrpPages![Categoryname] = Me![Text27]
GrpPages![PageNumber] = Me.Page
GrpPages.Update
End If
GetGrpPages
ErrHandler:
If Err.Number <> 0 Then
MsgBox Err.Number & ": " & Err.description
Err.Clear
Exit Sub
End If
End Sub
Private Sub Report_Open(Cancel As Integer)
CurrentDb.Execute "delete * from [CategoryGroupPages];", 0
Set GrpPages = CurrentDb.OpenRecordset("C
GrpPages.Index = "primarykey"
End Sub
Category Group Pages is a table created in Access with Category Name and PageNumber as fields in the report with category name as PK.
Any tip to solve this issue is highly appreciated
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hmm... I'll check on this. I used this exact same MSKB and it worked for me wonderfully.
-Silly-
-Silly-
ASKER
Thanks