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.
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.
Comments (3)
Commented:
Michael
Commented:
Dell Customer Service
Commented: