Results 1 to 6 of 6
  1. #1
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    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.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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
    Andrew

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,651
    Thanks
    38
    Thanked 161 Times in 139 Posts
    Very minor point.

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

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Ian B Brown View Post
    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!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •