Breaking up a report or looping through?

[tonyaccess]

Hi, I hope you may be able to help a coding novice.

I have a fairly simple db which extracts data to a report via a query,
my problem is the report is three pages per record set and a total of
3000 sets. When I send the report to our printer/copier for stapling,
Access and the printer think it's one job and treats the report as one
document instead of 3000 sets of three pages, and tries to staple the
whole job together. I think I need to loop through the query data and
send it through the report one at a time. I also believe I need to have
an autonumber field in the query for reference, which I am not sure how
to create.

I need to keep it within Access because the report contains graphics
which get lost when exporting. Page breaks don't work as the report is
still one big document.

My experience with coding stops with creating a new module, so any help
would be gratefully received.

Thanks, Slotz

[Salad]

to********@hotmail.co.uk wrote:

Hi, I hope you may be able to help a coding novice.

I have a fairly simple db which extracts data to a report via a query,
my problem is the report is three pages per record set and a total of
3000 sets. When I send the report to our printer/copier for stapling,
Access and the printer think it's one job and treats the report as one
document instead of 3000 sets of three pages, and tries to staple the
whole job together. I think I need to loop through the query data and
send it through the report one at a time. I also believe I need to have
an autonumber field in the query for reference, which I am not sure how
to create.

I need to keep it within Access because the report contains graphics
which get lost when exporting. Page breaks don't work as the report is
still one big document.

My experience with coding stops with creating a new module, so any help
would be gratefully received.

Thanks, Slotz

Let's say you have a table called Customers and you want the report to
print the report for each customer. You could do something like this...
Dim rst As DAO.Recordset
Dim strSQL As String
Dim intCurrent As Integer

'sort the table by customer name
strSQL = "Select CustomerID, CustomerName From Customers " & _
"Order By CustomerName"
Set rst = Currentdb.OpenRecordset(strSQL,dbopensnapshot)
If rst.RecordCount > 0 then
Docmd.Hourglass = True
'move last to get the record count
rst.MoveLast
rst.MoveFirst
Do while not rst.EOF
intCurrent = intCurrent + 1
Me.RecMsg = intCurrent & "/" & rst.RecordCount
Me.CustMsg = rst!CustomerName
Docmd.OpenReport "TheReportName",,,"CustomerID = " & _
rst!CustomerID
rst.MoveNext
Loop
Docmd.Hourglass = False
Endif
rst.Close
set rst = Nothing
msgbox "Done"

I put in an hourglass but you could have something on the form that
informs the user how many records you've printed and what customer you
are printing. So I put in the Rec/Cust msg references.

I don't know how well this will work...the reason being is that you are
creating 3,000 separate reports; 1 report for each customer. That's a
lot of jobs to stuff into a print queue. I don't know if you'd need to
put in a pause function to print/pause for each customer. I guess
testing is the best way to do it.