?
Solved

Access Report Pagination

Posted on 2009-04-16
10
Medium Priority
?
483 Views
Last Modified: 2013-11-28
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
Comment
Question by:Rwardlow
  • 5
  • 3
  • 2
10 Comments
 
LVL 58
ID: 24162403
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 24164475
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
 
LVL 1

Author Comment

by:Rwardlow
ID: 24216696
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 58
ID: 24216796
<<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
 
LVL 1

Author Comment

by:Rwardlow
ID: 24217462
many thanks
0
 
LVL 1

Author Comment

by:Rwardlow
ID: 24252419
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
 
LVL 1

Author Closing Comment

by:Rwardlow
ID: 31571181
This worked great. Thanks
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24254980
I'll take a look tonight
0
 
LVL 1

Author Comment

by:Rwardlow
ID: 24255079
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24256081
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

830 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