How to print a report to a specific printer using the Printer Object.
Access Archon #131
Introduction
WAW reader Tim H. writes: “Unless my mind is starting to go, I remember being able to select a specific printer for a report in past versions of Access. The report was saved so that every time I printed the report, it would go to the appropriate printer (the downside being that if that printer wasn’t available, I would get an error message). Isn’t this possible any more? How can I specify that a particular printer must be used for certain reports?”
In older versions of Access (prior to Access 2000), you could select a specific printer for a report, and then the report would automatically print to that printer in future – convenient if you never changed the printer, but problematic if you got a new printer, or sent the database to someone else who had a different printer. Access 2002 (Office XP) added a new feature, the Printer object, that has changed the way printers are assigned to reports, making it much easier to programmatically assign a printer to a report (no more messing with the obscure PrtDevNames and PrtDevMode properties).
If you look at the properties of an Access 2002 or 2003 report in the Object Browser, you will see a Printer property. But this is not (as you might think) the printer selected for this report, but the current default printer for Access as a whole. To print a report to a specific printer (in VBA code), you need to change the default application printer; you can set it back to its previous value after printing the report, if desired.
To do this, first you need to know what the printer’s name is – the name it has in the Windows Registry, which may not be the name you call it. The following procedure lists the names for all printers installed in Windows, and their numbers as well (you will need the numbers when referencing a specific printer in code, or assigning printers in a value list for a combo box):
Public Sub ListPrinters()
Dim i As Integer
i = 0
For Each prt In Application.Printers
‘The printer number is listed for referencing the printer in code
Debug.Print i & ” – ” & prt.DeviceName
i = i + 1
Next prt
End Sub
On one of my computers (the one to which the printers are attached), the ListPrinters procedure yields the following list:
0 – WinFax (Photo Quality)
1 – WinFax
2 – Microsoft Office Document Image Writer
3 – HP LaserJet 3200 Series PCL
4 – HP Deskjet 9670 Duplex
5 – HP Deskjet 9670
6 – Fax
7 – ClickBook Printer
8 – Adobe PDF
On the other computer (where the same printers are accessed via my home network) the list is as follows:
0 – DELL_DIMEN_8300HP Deskjet 9670
1 – DELL_DIMEN_8300HP Deskjet 9670 Duplex
2 – DELL_DIMEN_8300HP LaserJet 3200 Series PCL
3 – Microsoft Office Document Image Writer
4 – ClickBook Printer
5 – Auto HP LaserJet 3200 Series PCL on DELL_DIMEN_8300
6 – Auto HP Deskjet 9670 Duplex on DELL_DIMEN_830
Note that only some of the entries represent actual printers – on the first computer, there one entry for my HP 3200 and two for my HP 9670, and on the second computer, there are two entries for the HP 3200 and four entries for the HP 9670. I have only one HP Deskjet 9670 printer – the HP Deskjet 9670 Duplex printer item represents the printer in Duplex mode, for printing with ClickBook, so there are only two physical printers. The numbers prefacing the printer names are needed to reference a printer in VBA code, but you don’t need to display them in the combo box drop-down list.
You can assure that a specific report will be printed to a specific printer by using code like the following procedure, which hard-codes both the report name and the printer name. This function can be run from the mcrAssignPrinter macro.
VBA Code
Public Function AssignPrinter()
On Error GoTo ErrorHandler
‘Save current default printer
Set prt = Application.Printer
Debug.Print “Current default printer: ” & prt.DeviceName
‘Select a specific printer as new default printer
Application.Printer = Printers(“HP Deskjet 9670”)
‘Print the report
DoCmd.OpenReport “rptEmployeePhones”
‘Set printer back to former default printer
Application.Printer = prt
‘Can hard-code the default printer name if desired
‘Application.Printer = Printers(“HP LaserJet 3200 Series PCL”)
ErrorHandlerExit:
Exit Function
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
Supporting Files
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch131.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
Printer Object.mdb |
Access 2002-2003 database |
Wherever you want |
