Printing a large report in 30 page groups?

[RMort]

I have a report that is 677 pages. Each page is a unique document. I would
like to print them in groups of 30 pages each. is this possible with some
VBA?

 

[Duane Hookom]

Does each page always display a specific number of records? If so, you could
add a counter to your record source to print X number of records equal to 30
pages.

 

[John Spencer]

Yes, it is possible. How kind of depends on how the records are selected and
if each record is one page.

Use a query to get 30 records at a time. One solution would be to use a
"work" table that hold the primary key of the records that have not been
printed and then select 30 records at a time from the "master" table. Code
snippet follows to give you some idea.

Function f()
Dim db As DAO.Database
Dim iCounter As Long
Dim strSQL As String

'Clear the work table
Set db = CurrentDb()
strSQL = "DELETE * FROM WorkTable"
db.Execute strSQL

'Loop through and call the report multiple times
Do
'The Report 's source would be something like
'SELECT TOP 30 *
'FROM SomeTable LEFT JOIN WorkTable
'on SomeTable.PK = WorkTable.PK
'WHERE WorkTablePk Is Null
'ORDER BY <ListFields that determine the print order plus the PK>

'Call the report
DoCmd.OpenReport "TheReport", acViewNormal

'============================================================================
' You may need to introduce a delay here to allow the report to
' generate and be sent to the printer
'============================================================================

'Update the work table with the newly printed records
strSQL = "INSERT Into WorkTable (PK) " & _
" SELECT TOP 30 PK " & _
" FROM SomeTable LEFT JOIN WorkTable " & _
" ON SomeTable.Pk = WorkTable.PK " & _
" WHERE WorkTable.PK is Null " & _
" ORDER BY <ListFields that determine the print order plus the PK>"

db.Execute strSQL
iCounter = db.RecordsAffected
Loop Until iCounter <> 30

End Function

One problem is that the above will loop one extra time and print a report with
no records if the records are exactly divisible by 30.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County