Dynamic Printer Selection for Access

Helen Feddema
CERTIFIED EXPERT
Published:
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.

Introduction


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)

Fax

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
         .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.

 

1
3,446 Views
Helen Feddema
CERTIFIED EXPERT

Comments (3)

Commented:
Thank you Helen. Just what I needed to solve printer selection issues...

Michael
Excellent! I was finding the solution of printer selection issue. I found this article and followed steps as given. And finally got success. Such a great article.
Dell Customer Service
Hello, 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.

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.