Download Sample Access Database: New Style Main Menu with Dynamic Printer Selection.accdb
If you have more than one printer available, you might want to have a choice of which printer to use when printing a report or a Word document filled with Access data. For your own computer, you can hard-code the printer choice, after getting a list of the printer names as used in code. The ListPrinters function in the above sample database (New Style Main Menu with Dynamic Printer Selection.accdb) writes this list to the Immediate window when run on one of my computers:
Send To OneNote 2010
Microsoft XPS Document Writer
Microsoft Print to PDF
HP0AC7A8 (HP Officejet 6700)
HP Officejet 6700 (Network)
HP LaserJet Pro MFP M127fw
HP LaserJet Pro MFP M127-M128 PCLmS
Fax - HP Officejet 6700 (Network)
The first thing to note about this list is that it includes a number of items that are not actually printers, such as Fax and Adobe PDF. The second thing to notice is that if you run this code on your computer, you will get a different list.
Therefore, if you write code such as the segment below, which I previously used to get a choice of printer, the code will fail if you replace a printer, or if the database is used on another computer.
If intChoice = 1 Then strPrinter = "HP LaserJet Pro MFP M127fw" appWord.ActivePrinter = strPrinter ElseIf intChoice = 2 Then strPrinter = "HP Officejet 6700 (Network)" appWord.ActivePrinter = strPrinter End If
What is needed is a way to provide a choice of printers available on the current computer, so that a valid printer can be selected and used without hard-coding its name. To accomplish this, I modified the ListPrinters code to write printer names to a table, tblPrinters, with a supporting table, tblExcludeWords, which lists the words used to exclude non-printer objects such as Fax from the drop-down Select Printer combo box (this table can be modified as desired). The WritePrintersToTable procedure is listed below:
Public Sub WritePrintersToTable() 'Created by Helen Feddema 10-Mar-2017 'Last modified by Helen Feddema 29-Jun-2017 On Error GoTo ErrorHandler Dim prt As Printer Dim rstExcludeWords As DAO.Recordset Dim rstPrinters As DAO.Recordset Dim strExcludeWord As String Dim strPrinterName As String Dim strSQL As String 'Clear old printer names from table strSQL = "DELETE * FROM tblPrinters;" CurrentDb.Execute strSQL Set rstPrinters = CurrentDb.OpenRecordset("tblPrinters") Set rstExcludeWords = CurrentDb.OpenRecordset("tblExcludeWords") For Each prt In Application.Printers strPrinterName = prt.DeviceName Debug.Print "Testing printer name: " & strPrinterName With rstExcludeWords .MoveFirst Do While Not .EOF strExcludeWord = ![ExcludeWord] Debug.Print "Checking exclude word: " & strExcludeWord _ & " for " & strPrinterName If Nz(InStr(strPrinterName, strExcludeWord)) > 0 Then Debug.Print strExcludeWord & " found in " & strPrinterName GoTo NextPrinter End If .MoveNext Loop End With With rstPrinters .AddNew ![PrinterName] = strPrinterName ![ComputerUser] = Environ("USERNAME") .Update End With NextPrinter: Next prt ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number _ & " in WritePrintersToTable procedure; " _ & "Description: " & Err.Description Resume ErrorHandlerExit End Sub
This procedure is run from the Load event of the database's main menu, so that the cboSelectPrinter combo box will have a list of the current printers. The main menu of the sample database is based on my New Style Main Menu, originally described in Access Archon article #212.
I have updated the code in that database that switched the main menu between a version for Access 2003 and one for Access 2007 (the original database was created when Access 2007 was new) – now the assumption is that the database is opened in Access 2010 or higher.
I have also added several new features: The Word template used to create documents filled with Access data on the Customers form uses the embedded template technique described in my Delivering a Word Template article at Experts Exchange, and the printer selector combo box (and supporting tables and code) are new features.
Once a printer has been selected, its name is saved to a custom database property for use throughout the database.
strPrinterName = Nz(Me![cboSelectPrinter].Value) strPropertyName = "PrinterName" lngDataType = dbText Call SetProperty(strPropertyName, lngDataType, strPrinterName)
(See the New Style Main Menu Access Archon article, or my Experts Exchange article on Custom Database Properties, for full information on the use of custom database properties for saving and retrieving data.)
Access and Word handle printers differently, so the code for using the selected printer when printing an Access report is different than the code used to print a Word document. The cmdReports Click event procedure has a Select Case statement to handle the user's selection of Preview, PDF or Print to in the Report Mode options group.
If Print to is selected, the printer selector combo box is enabled for choice of a printer. The report name and selected printer name are used to set the arguments of the PrintToSelectedPrinterAccess procedure:
strPrinterName = GetProperty("PrinterName", "") all PrintToSpecificPrinterAccess(strPrinterName, _ strReportName)
The called procedure is listed below:
Public Sub PrintToSpecificPrinterAccess(strPrinter As String, strReport As String) 'Created by Helen Feddema 12-Feb-2010 'Last modified by Helen Feddema 28-Jun-2017 On Error GoTo ErrorHandler Dim prtCurrent As Printer Dim prtDefault As Printer 'Save current default printer Set prtDefault = Application.Printer Debug.Print "Current default printer: " & prtDefault.DeviceName 'Select a specific printer as new default printer Application.Printer = Printers(strPrinter) 'Print the report DoCmd.OpenReport strReport 'Set printer back to former default printer Application.Printer = prtDefault ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number _ & " in PrintToSpecificPrinterAccess procedure; " _ & "Description: " & Err.Description Resume ErrorHandlerExit End Sub
When printing a Word letter created from a template and filled with data from Access, the syntax is different. After creating the letter and filling its document properties with data from Access, this line of code does the printing:
Call PrintToSpecificPrinterWord(appWord, strPrinterName, doc)
This procedure is listed below:
Public Sub PrintToSpecificPrinterWord(appWord As Word.Application, _ strPrinter As String, doc As Word.Document) 'Created by Helen Feddema 12-Feb-2010 'Last modified by Helen Feddema 27-Jun-2017 On Error GoTo ErrorHandler Dim strDefaultPrinter As String 'Save current default printer strDefaultPrinter = appWord.ActivePrinter Debug.Print "Current default printer: " & strDefaultPrinter 'Select a specific printer as new default printer appWord.ActivePrinter = strPrinter 'Print the current document doc.PrintOut 'Set printer back to former default printer appWord.ActivePrinter = strDefaultPrinter ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number _ & " in PrintToSpecificPrinterWord procedure; " _ & "Description: " & Err.Description Resume ErrorHandlerExit End Sub
When printing from Access, you set the selected printer as the new default printer, print the report, then reset the default printer back to its original setting. In Word, you set the selected printer as the active printer, print the document, then set the active printer back to its original setting.
With this printer selection technique, you can safely deliver a database to a client, who will then be able to select a printer from those available on the computer in use, without having to rewrite your code for every computer on which it might be run.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.