• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

Access Report Pagination

I am printing a lab report that contains many 1, 2 or 3 page reports to different people.
I am using a group header for their property address (1 per person)
In the VBA code for the group header I am setting Page = 1 to begin each persons report at page 1.
I would like to use the "of" page number function. (Page 1 of 2)
But the "of" number is the number of pages in the entire report for everyone. (Page 1 of 63 for example)
Is there a way to calculate the "of number" for each group?
What is the syntax for that command?
Thank you in advance for your help.
Bob
0
Robert Wardlow
Asked:
Robert Wardlow
  • 5
  • 3
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
See the following MSKB article:
How to reset the page number and the total page count for each group in a Microsoft Access report
http://support.microsoft.com/kb/841779
  Which shows you how to activate a second pass on the report allowing you to pick up the last page of each group (you can use this technique for a lot of different things too, such as table of contents, indexes, etc).
JimD.
0
 
Jeffrey CoachmanMIS LiasonCommented:
You could also loop through the "People" and print a filtered report for each of them, one after another.

Something like this:

Private Sub cmdPrint_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT EmployeeID,EmployeeName FROM tblEmployees")
    rst.MoveFirst
        Do While Not rst.EOF
            DoCmd.OpenReport "rptOrdersByEmployee", acViewNormal, , "EmployeeID=" & rst!EmployeeID
            rst.MoveNext
        Loop
        MsgBox "Done", vbInformation
'Recordset cleanup
rst.Close
Set rst = Nothing    
       
End Sub

Sample attached

JeffCoachman

Access--Print-Individual-Group-r.mdb
0
 
Robert WardlowPresidentAuthor Commented:
Thank you both for your suggestions. I am trying the solution from JDettman first.

JDettman:
The instructions go step by step but
There is one part of the instructions that I don't know how to do.

It says "Define a variable for a Database Object, a variable for a RecordSet object" but it doesn't say where.
It says to "append the following code to any existing code"

Can I literaly put it anywhere?
Like in the group header or footer of the report?
Or does it go in a database module? If so, which one.

I don't understand how it can go anywhere?

Thanks in advance
Bob

 
0
Ultimate Tool Kit for Technology Solution Provider

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 now.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<It says "Define a variable for a Database Object, a variable for a RecordSet object" but it doesn't say where.>>
  You would define those in the report Declarations section.  Open the report in design view, select view/code.  The declarations section is right at the top of the code window.  Variables dim'd here are available to all procedures in the report and will exist for the life of the report.
<<It says to "append the following code to any existing code">>  
  It's talking about the reports code.  You may or may not have existing code.
JimD.
0
 
Robert WardlowPresidentAuthor Commented:
many thanks
0
 
Robert WardlowPresidentAuthor Commented:
JeffCoachman,
I am trying to use your solution by modifying your code to match my structure. But I think I have a syntax problem that I can't seem to figure out. Below is my code

[Results Mail Full] is a query
[Property Address 1] is a field in [Results Mail Full] query

I want to print 1 report for each property address

If I specify the address like below it works:
"[Property Address 1]= '123 main street'"

But this doesn't work
"[Property Address 1]=" & rst![Property Address 1]

I get this error
Runtime error 3075
Syntax Error (Missing Operator)

What is the correct syntax?

thank you in advance for your help.
Bob

 Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("SELECT [Results Mail Full].[property address 1] FROM [Results Mail Full]")
            rst.MoveFirst
            Do While Not rst.EOF
                DoCmd.OpenReport "Result full", acViewPreview, , "[Property Address 1]=" & rst![Property Address 1]
            rst.MoveNext
            Loop
            MsgBox "Done", vbInformation
            'recordset cleanup
            rst.Close
            Set rst = Nothing
0
 
Robert WardlowPresidentAuthor Commented:
This worked great. Thanks
0
 
Jeffrey CoachmanMIS LiasonCommented:
I'll take a look tonight
0
 
Robert WardlowPresidentAuthor Commented:
Thanks, but I figured it out. That's why I closed the question and awarded you the points. I was not able to get the other solution to work but I got yours to work.

Thank you both again for your help.
Bob
0
 
Jeffrey CoachmanMIS LiasonCommented:
Thanks,

For the record, JDettman's code link will work.
I had used it a few times.
I thought I had a sample of it, but I cannot find it now.

I use this now.
The code is more complex, but you can basically drop it in, and it will work.
(This code is for a Report Grouped by ShipCountry)

'************ 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
'

Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer

Private Sub PageFooterSection_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!ShipCountry
        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
        If Me.Page = Me.Pages Then
            Me!ctlGrpPages = ""
        Else
            Me!ctlGrpPages = Me.ShipCountry & " " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)
                If GrpArrayPage(Me.Page) + 1 = GrpArrayPages(Me.Page) Then
                    Me.txtPageHeader.Visible = False
                Else
                    Me.txtPageHeader.Visible = True
                End If
           
        End If
    End If
     
    GrpNamePrevious = GrpNameCurrent


I still feel that you should give the link Jim piosed another try.

My soultion is basically a workaround I came up with for people who were a little afraid of code.
It is slower than the code approaches because it sends one print job to the printer for every group. (Instead of one print job for the entire report)

;-)

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now