?
Solved

MS Access Reports  (Reset Page Number and total page count for each section of a report

Posted on 2004-03-30
3
Medium Priority
?
2,756 Views
Last Modified: 2013-11-28
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(Cancel 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("CategoryGroupPages")
    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

0
Comment
Question by:sramkris
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
Mighty_Silly earned 300 total points
ID: 10720199
un-remark this line
 remove this --> < '  > GrpPages.Seek "=", Trim(Me!Text27)
0
 
LVL 1

Author Comment

by:sramkris
ID: 10726400
I had unremarked this line but the result was the same so i tried calling the function..Is there something which is not getting set..

Thanks

0
 
LVL 11

Expert Comment

by:Mighty_Silly
ID: 10726844
hmm... I'll check on this.  I used this exact same MSKB and it worked for me wonderfully.

-Silly-
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question