How to automatically print out an Access Report using VBA

APPLIES TO:  ACCESS 2007, 2010 (UNTESTED IN EARLIER VERSIONS)

The following MS Access tutorial shows you how you can use VBA to create a button to print out a MS Access report and how to print out an MS Access report automatically based on a specific data.  Both of these tasks can of course be done easily using a macro; however the benefit of being able to do them using VBA is that you can build them into other functions that you may be writing.

This MS Access tutorial assumes that you have created a report called rptCustomerInvoice that you want to print out automatically.  You can of course change the invoice name to the name of your own report.

1. Create your button on your form and call it btnPrintDoc

2. With your new button selected click in the tools section of the “Design” tab click on the item called “Property Sheet”

3. Select the “Event Tab”

4. On the “On Click” property click on the little black down arrow / triangle

5. Select “Event Procedure”

6. Click on the three dots next to the little black down arrow / triangle – this will open up the VBA window and you should see the following:

Private Sub btnPrintDoc_Click()

End Sub

7. On a new line directly between the two lines above add the following:

 

DoCmd.OpenReport  “rptCustomerInvoice”, acViewPreview

DoCmd.PrintOut , , , , 1

(the number 1 is the number of copies to print)

8. Save your changes and test your button.  Your report should now open in preview mode and automatically print.

9. If as with an invoice you want the report to only print data for a specific customer you can replace the code in item 7 with the following:

 

DoCmd.OpenReport “rptCustomerInvoice”, acViewPreview, , “invoice_id = ” & Me.invoice_id

DoCmd.PrintOut , , , , 1

 

(the above assumes that your report is based on a table or query that has a field called invoice_id and that the form your button is located on also has a field called invoice_id).

10. Finally if the report you want to print can differ depending on the circumstances, you can replace the report name with a string variable as demonstrated in the code below:

Private Sub btnPrintDoc_Click()

Dim strDocName as String

If Me.Dealer = True Then

strDocName = “rptDealerInvoice”

DoCmd.OpenReport strDocName, acViewPreview, , “invoice_id = ” & Me.invoice_id

DoCmd.PrintOut , , , , 2

Exit Sub

Else

strDocName = “rptCustomerInvoice”

DoCmd.OpenReport strDocName, acViewPreview, , “invoice_id = ” & Me.invoice_id

DoCmd.PrintOut , , , , 2

Exit Sub

End If

End Sub

Please feel free to contact us at derek@creativeconnections.co.uk for help or advice on any aspect of this tutorial or MS Access in general, we love feedback and are always happy to help.