Printing, Collating & Stapling MS Access Batch Reports

Very recently I came across a Xerox 5665 printer, which obviously can print, but then collate and staple documents. This is a very efficient and intellgent machine in its class to do all these features.

Xerox 5665

Xerox 5665

Now since the application that I am dealing with generates 150-200 reports, this machine was catching to my expectation in terms of speed, but when it came to collate & stapling 2 copies of these reports, it was not living up to the expectation.

After talking to Xerox I found that, it was our Microsoft Access 2000 application that was creating the trouble. Because Access was already collating this document and then sending it to Xerox machine, its being treated as 1 report of 200 pages.

To turn around this issue, I had to change the report creation & printing routine in MS Access. Basically we wanted 2 copies of each report, collated & stapled together. I had to make sure each print job was sent separately of 2 copies.

If I send 1 copy of report from access and asked the printer to make 2 copies, then it would print 2 separate copies and if its a 2 pager report, it will staple them. But this was not I my need, I wanted to print 2 copies and staple both together.

The following VBA code was generated which looked up a query of all the invoices that needs to be generated and pull each report and print 2 copies of it. Now since VBA code is telling to print 2 copies, the Xerox printer treat it as 1 document and staples them.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strWhere As String
Set db = CurrentDb
Dim checkinv As String
checkinv = MsgBox("Do you wish to print batch reports?", vbYesNo, "Question")

If checkinv = vbYes Then

Set rs = db.OpenRecordset("qryRptCount")
With rs
.MoveFirst
Do Until .EOF
DoCmd.OpenReport "rptReports", acPreview, "", "InvNo = " & !InvNo
DoCmd.PrintOut acPages, , , , 1, 1
DoCmd.Close
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
Else
Exit Sub
End If

Leave a Reply

Your email address will not be published. Required fields are marked *