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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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..
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HKFueyAuthor Commented:
Thanks for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.