VBA to change default printer

I want to change my default printer, print some labels then change it back.

I found this but it does not work (See error Pic)

     Dim prt As Printer
    ' Get current default printer
    Set prt = Application.Printer
    ' Set default printer
    Application.Printer = Application.Printers(strPrinterName)

Open in new window

Can anyone help?Error.png
Who is Participating?
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
strPrinterName ="My Printer Name"

Open in new window

How are yu getting the printer name.

I load a combo box or list box from  the Application.Printers collection. The user selects the desired print er, This way I am sure i have the correct name used by the user's PC.
Rey Obrero (Capricorn1)Commented:
That is not as simple as it seems, and is not consistent across the Office programs.
When you screw with the ActivePrinter in Word, it actually changes your Windows default printer

Access DOES NOT behave the same way
Application has printers, as you've seen
So does each printable object (forms and reports)
You could set your report's printer to what you want and leave the application printer alone

Here's how I do that for a printer I have with 'label' as part of its name

'get the proper device name into a string
Dim ptr As Printer
Dim ptrstring As String
Dim HaveLabelPrinter As Boolean
For Each ptr In Application.Printers
    If ptr.DeviceName Like "label" Then
        ptrstring = ptr.DeviceName
        HaveLabelPrinter = True
        Exit For
    ElseIf ptr.DeviceName Like "*label*" Then
        ptrstring = ptr.DeviceName
        HaveLabelPrinter = True
    End If
Next ptr

If HaveLabelPrinter = False Then
    MsgBox "You don't have the label printer installed.  Ask Nick67 about that"
    Exit Sub
End If

Dim stDocName As String
stDocName = "rptBillingLabel" 'my report
'Open the report in designview, hidden
DoCmd.OpenReport stDocName, acViewDesign, , , acHidden
Reports(stDocName).Printer = Application.Printers(ptrstring)
'print it
DoCmd.OpenReport stDocName, acNormal
'don't save any changes
DoCmd.Close acReport, stDocName, acSaveNo

Open in new window

This stuff is PARTICULARLY finicky.
I can get an Okidata Microline 320 to print 3.5" x 1" address labels from a continuous feed, but if I open or change the report...
Access tries its damnedest to revert to a letter-size page and mess stuff up
So on each computer with a label printer, I have to create a custom form in the Print Server bits of the Printers Control panel, and then run Access and get VBA to spit that number out, and then in code I didn't post, I force the label printer to use the custom paper size, no margins and the tractor feed.

I cringe when a new label printer, new computer, or change to the label occurs.

Access can print labels
It just ain't easy.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
HKFuey ,

I use the code you posted to set the applicant print all the time.

The key to the code is how you set the value in strPrinterName.

How are you setting the value for strPrinterName?

If the label printer  will not change, set the report to always print to that printer not the default printer..
HKFueyAuthor Commented:
Hi Boyd,
Re: How are you setting the value for strPrinterName?

Dim strPrinterName
strPrinterName ="My Printer Name"

I only have 4 reports and 2 label printers, I'm beginning to think the simplest way is to set the printer on the report and just let the user choose which format they want.
HKFueyAuthor Commented:
Thanks for the help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.