Change report printer programmatically in Access 2016

Question

What's the best way to programmatically change the printer for a report in Access 2016?

I'm running an Access 2016 app as a RemoteApp. It has several reports that print to specific printers (not default) and paper trays. Users from different locations run the app. Depending on the user's location the reports must print to a different printer. What's the best way to change the report printer in code? (All printers are on the network and are installed on the rdp server.)

TIA for any suggestions,

Mike

Answer 1

>>>What's the best way to programmatically change the printer for a report in Access 2016?

According to your description, you should be able to do something like this:

Sub SwitchPrinter()
     Dim prt As Printer
     ' Get current default printer
     Set prt = Application.Printer
     ' Set default printer
     Application.Printer = Application.Printers("OtherPrinter")
     ' Print something, e.g.
     DoCmd.PrintOut
     ' Restore original printer
     Set Application.Printer = prt
 End Sub

In addition list the names of available printers, run the following code:

Sub ListPrinters()
     Dim prt As Printer
     For Each prt In Printers
         Debug.Print prt.DeviceName
     Next prt
 End Sub

For more information, click here to refer about How to reset changes to the Application.Printer object
and here to refer about Application.Printer Property (Access)

Answer 2

Thanks for the suggestion, David. But I'm concerned that changing the application default printer will not work because each report has specified paper trays. Each report has its own specified paper tray. If I change the report to use the default printer, then programmatically change the application default printer, wouldn't that wipe out the report's paper tray setting?

I wonder if there's a way to programmatically change just the report's specified printer (under page setup). 

Answer 3

Mike I think this will do it.

    Dim rpt As Report
    DoCmd.OpenReport ReportName:="rptOrders", View:=acViewDesign, WindowMode:=acHidden
    Set rpt = Reports!rptOrders
    rpt.Printer = Application.Printers("myPrinterName")
    DoCmd.Close acReport, "rptOrders", acSaveYes
    Set rpt = Nothing

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 4

Perfect! Does exactly what I need. Thanks.

Answer 5

You're welcome.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 6

Bill,

I closed this thread too soon. My code is not saving the changed printer setting. Here's a sample of what I'm trying to do. Before running code, the report's printer is Printer1 and I'm trying to change it to Printer2. As you can see from the debug.print results, the printer is changed while the report is open, but the change is not saved.

Dim strReport as String
Dim strPrinter as String
Dim rpt as Report
Dim strPrinterName as String

strReport = “MyReport”
strPrinter = “Printer2”
         
'open report in design view to set printer
DoCmd.OpenReport ReportName:=strReport, View:=acViewDesign, WindowMode:=acHidden
            
Set rpt = Reports(strReport)

'get the current printer device name
strPrinterName = rpt.printer.DeviceName
Debug.Print strPrinterName

‘change printer
rpt.printer = Application.Printers(strPrinter)

‘get printer name again
strPrinterName = rpt.printer.DeviceName
Debug.Print strPrinterName

'close and save
DoCmd.Close acReport, strReport, acSaveYes
            
'open report again to see if new printer was saved
DoCmd.OpenReport ReportName:=strReport, View:=acViewDesign, WindowMode:=acHidden

Set rpt = Reports(strReport)

'get the current printer device name
strPrinterName = rpt.printer.DeviceName
Debug.Print strPrinterName

DoCmd.Close acReport, strReport, acSaveNo
             
=====================
IMMEDIATE WINDOW

Printer1

Printer2

Printer1

Answer 7

Mike - Isn't that just like Microsoft? I tested and you are right. The change is not saved. I'll see if I can find another way to do it.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 8

Mike

According to this MSDN article, you should be able to save the printer in any view.

Saving Printer Settings with a Form or Report

Whether a form or report uses the settings of the default application printer (the settings managed with the Application object's Printer object) is determined by whether the form or report has previously saved printer settings. Printer settings for a form or report can be saved two ways:
A user can save printer settings by opening the form or report in any view, and using the Print or Page Setup dialog boxes to change the settings for the form or report.
You can make changes to the Printer object of a form or report in code, and those changes will be saved with the form or report if you use the Save method before closing the form or report, or specify acSaveYes for the Save argument when using the Close method to close the form or report

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 9

Thanks for the response, Bill. I know that's how it should work, but it doesn't for me. Look at the code sample I posted. It displays (using debug.print) the report printer devicename to see the initial setting, then it changes the printer, then it displays the devicename again to show that the printer setting changed. Then it saves the report with acSaveYes. Then it opens the report again and displays the devicename. The printer setting was not saved. The setting has gone back to the original printer.

I also tried saving by using the Save method before Close. Still didn't save the printer setting. I confirmed all of this by opening the report manually and looking at Page Setup, and the printer was still set to the original.

I would appreciate any suggestions about where I should look in my code for the problem, or anything else that might explain this.

Thanks much in advance.

Answer 10

Mike - I'm sorry I can't pursue this. I have only one printer at home and at work we use a network of servers that queues up my print jobs so I can print them at any printer after I badge in. So that is technically "one" printer, too.

What happens if you open the report in preview instead of design? If that also fails you might have to try assigning the printer in the report's Open event each time it is printed.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 11

Thanks, Bill. Understood. I appreciate the time you've already spent looking into it. I'll try opening in Preview, and using Open event when printing. Good ideas.

Answer 12

Sorry to report that you can't change printer setting using OnOpen event. That's sad--I thought that would be the most elegant way to accomplish a temporary, on-the-fly printer change for any specific report.

Answer 13

>>>Sorry to report that you can't change printer setting using OnOpen event. That's sad--I thought that would be the most elegant way to accomplish a temporary, on-the-fly printer change for any specific report.

According to your description, I suggest that you could refer to Choosing Output Devices at Runtime in Access 2007

Please correct me if I have any misunderstandings on your question.
Thanks for your understanding.