Access VBA Print DoCmd

[StacyRacca]

I'm a begginer at Access VBA and I'm trying to right simple code to get access to print a report to a specific network printer. Can anyone help with a simple VBA print command for hitting a network printer?

 

[Andrew Fergus]

Hi Stacy

I have tested the following code over a couple of network printers and it seems to work ok. I have seen all sorts of complicated code over the internet but what I have written seems to be the simplest solution - so hopefully this works for you too.

You need to have the other network printer installed on the your PC and you refer to the printer by its name (that you would see under the 'Printers' screen in Windows).

I invoked this code by placing a button on a form and attaching this code to that button:

Private Sub MyButton_Click()

Dim defPrinter As String, NewPrinter As Printer

'Get the default printer name
defPrinter = Application.Printer.DeviceName

'Create a new printer object
Set NewPrinter = Application.Printers("MyPrinter") 'use your printer name here

'Set the default printer to the new printer
Set Application.Printer = NewPrinter

'Open the report
DoCmd.OpenReport "MyReport", acViewNormal  'use your actual report name here

'Print the report (the next line seems to print the report and the form so I removed it, if your report doesn't print then remove the apostrophe on the next line)
'DoCmd.PrintOut

'Close the report
DoCmd.Close acReport, "MyReport", acSaveNo  'report name again!

'Reset the printer back to the default printer
Set NewPrinter = Application.Printers(defPrinter)
Set Application.Printer = NewPrinter

HTH, Andrew

 

[StacyRacca]

Andrew...works perfectly....thank you very much.

 

[cooksc]

You're great!!! I loaded your code, followed the VERY clear instructions and the code ran perfectly. Thanks so much

My question is: I need to print a PDF file, which worked great using your code, however I would like to know how have the file/report print without prompting me for a location and file name. Can this be written into the code and have the file overwrite the existing file. Also, would need to have code that allows the enough time for the file to print before executing the rest of code.

Thanks in advance for you help.
Steve