Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Dynamic Printer Selection for Access

Published on
3,389 Points
Last Modified:
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with the Access and Word Printers collections.


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)


Adobe PDF

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
         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
      End With
      With rstPrinters
         ![PrinterName] = strPrinterName
         ![ComputerUser] = Environ("USERNAME")
      End With
   Next prt
   Exit Sub
   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, _

 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
   Exit Sub
   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
   'Set printer back to former default printer
   appWord.ActivePrinter = strDefaultPrinter
   Exit Sub
   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.


Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Join & Write a Comment

In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month