Access & Printers

Topic

New Reply

Hello Fellow Loungers,

I have a database I developed for our Home Owners Association and it is severely locked down because one of the main users is computer challenged.
After completing the final lock down the graphic below is what the user sees. The printer Icon uses the Windows Default printer. However, there are a couple of reports which need to be sent to a PDF printer so they can be posted on our website. I didn’t realize this problem until the first time they tried to do it after the final lockdown. After some poking around the Access VBA help files I came up with a solution which y’all may find useful.

Add this code to a standard module:

Code:

Option Compare Database
Option Explicit

Public strDfltPrt As String   '*** Save Default Printer Name for resetting ***

'                          +---------------------+                 +----------+
'--------------------------|  SwitchPrinters()   |-----------------| 07/30/10 |
'                          +---------------------+                 +----------+
'Called by     : Form_Open()  - From any form!
'                Form_Close() - From any form!
'Calls         : N/A
'Function Calls: N/A
'Globals Used  : N/A

Sub SwitchPrinters(zSwitchToPtr As String)

  Dim prtName As Printer
  Dim iPrtNo  As Integer
  
  iPrtNo = 0
  
  For Each prtName In Application.Printers
     If prtName.DeviceName = zSwitchToPtr Then
       Exit For
     Else
       iPrtNo = iPrtNo + 1
     End If
  Next prtName

'*** Uncomment next 2 lines for testing or visual verification of switch ***
'  MsgBox "Printer Selected: " & Format(iPrtNo, "#0") & _
'         " " & Application.Printers(iPrtNo).DeviceName
         
  Application.Printer = Application.Printers(iPrtNo)


End Sub    '*** SwitchPrinters ***

Add the following code to each Report that needs to go to a different printer:

Code:

Option Compare Database
Option Explicit

'                          +---------------------+                 +----------+
'--------------------------|    Report_Open()    |-----------------| 07/30/10 |
'                          +---------------------+                 +----------+
'Calls         : SwitchPrinters
'Function Calls: N/A
'Globals Used  : strDfltPrt

Private Sub Report_Open(Cancel As Integer)

  strDfltPrt = Application.Printer.DeviceName
  SwitchPrinters "PrimoPDF"  '*** Subistitute Desired Printer Name Here ***

End Sub

'                          +---------------------+                 +----------+
'--------------------------|   Report_Close()    |-----------------| 07/30/10 |
'                          +---------------------+                 +----------+
'Calls         : SwitchPrinters
'Function Calls: N/A
'Globals Used  : strDfltPrt

Private Sub Report_Close()

  SwitchPrinters strDfltPrt
  
End Sub

Or If you want to allow interactive selection of printers or just for testing purposes you can add this code to a standard module:

Code:

'                   +----------------------------+                 +----------+
'-------------------|UserSelectPrinterByNumber() |-----------------| 07/30/10 |
'                   +----------------------------+                 +----------+
'Called by     : Form_Open()  - From any form!
'                Form_Close() - From any form!
'Calls         : N/A
'Function Calls: N/A
'Globals Used  : N/A

Sub UserSelectPrinterByNumber()

  Dim prtName As Printer
  Dim iPrtNo  As Integer
  Dim zPrtMsg As String
  
  iPrtNo = 0
  zPrtMsg = "No.  Printer Name" & vbCrLf
  
  For Each prtName In Application.Printers
     zPrtMsg = zPrtMsg & Format(iPrtNo, "#0") & "  " & prtName.DeviceName & vbCrLf
     iPrtNo = iPrtNo + 1
  Next prtName

  iPrtNo = Val(InputBox(zPrtMsg, "Printer Selection Dialog"))
  
  MsgBox "Printer Selected: " & Format(iPrtNo, "#0") & _
         " " & Application.Printers(iPrtNo).DeviceName
         
  Application.Printer = Application.Printers(iPrtNo)
  
End Sub   '*** UserSelectPrinterByNumber ***

If anyone has ideas to improve this code I’d love to hear from you.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Replies

I would change the UserSelectPrinterByNumber sub to populate a table then use a combo box to select them. But that’s me

You have done well though.

Reply | Quote

You might be interested in lebans pdf utility

I have used it.
You can send direct to PDF file even if no PDF printer or software is installed on the machine
Everything you want can be found at

Find It Here

This is for earlier versions of Access before 2007/2010 although it works just fine in them as well.
You can even add code to then directly email the pdf

Reply | Quote

Andrew,

Thanks for the link. It seems like a very Access specific tool but with a lot of power.

I think I’ll stick with PrimoPDF as it’s free and works with everything. I’ve been very satisfied with it.

I may look into it later as we’re considering emailing our annual dues billings, but that’s a ways off.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Very minor point.

In the Report_Open sub you are declaring variables that aren’t being used.

Reply | Quote

Very minor point.

In the Report_Open sub you are declaring variables that aren’t being used.

Ian,

Thanks, I forgot to clean them out when I moved the bulk of the code to the called procedure.
I’ll edit the original post to get rid of them.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote