Too easy to change from label printer to regular printer in Microsoft Access report.


Using Microsoft Access 2016 on  Windows 10

I need to print to a label printer for a report in Microsoft Access.  I have set the printer in Design mode for the report to TLP 2824 plus label printer.  If the printer is powered off, then the message (as shown in the png attachment) will pop up.   This popup will allow the user to change the printer settings for this report.

I wish to intercept the message with my own message and cancel the operation.  My code below is not correct.  
I would appreciate any help.
Thank you.

On Error GoTo ErrHandler:
        DoCmd.OpenReport LabelReport, acViewPreview, , "[Tag] = -1", acWindowNormal, OpenArgs: '[tbl QuickLabels]![Tag] is boolean
        DoCmd.SelectObject acReport, LabelReport
        DoCmd.RunCommand acCmdZoom100
        Exit Sub
    MsgBox ("It looks like the " & Chr(34) & "TLP 2824" & Chr(34) & " printer is missing or not powered up." & vbCrLf & "Operation cancelled.")
    Resume Next
 End Sub

Open in new window

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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You might check for the existence of that printer before you open the report, and then handle that. The error you're getting is a system error, and I'm not entirely sure you can intercept it.

You can try to intercept it, but (assuming you can) you'd have to do so in one of the Report events, not in the function that opens the report. You can try the Open or Load event of that report, or perhaps the Page, Resize or Activate event - I'm not sure.

To check for the existence of the printer, use the Printers collection:

Function CheckPrinter(PrinterName As String) As Boolean

    Dim prt As Printer
    Dim bFoundPrinter As Boolean

    For Each prt In Application.Printers
        If prt.DeviceName = PrinterName Then
            bFoundPrinter = True
            Exit For
        End If

    CheckPrinter = bFoundPrinter

End Function

Open in new window

Place that code in a Standard Module, then call it like this:

If Not CheckPrinter("YourPrinterName") Then
  Msgbox "The Printer does not exist"
End If

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
peispudAuthor Commented:
Thank you for your answer.

I use your code, then I would have to modify  the vba if I upgrade to another label printer.

Is it possible to detect the name of the name of the printer that the report is expecting before running your code?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is it possible to detect the name of the name of the printer that the report is expecting before running your code?
The report would have to be open before doing so, but I'm not sure exactly when that error is popping up. You could try using this immediately after your call to OpenReport:

If Reports("YourReportName").Printer.DeviceName <> "TLP 2824 plus label printer" Then
  '/ do whatever here
End If

I don't know if that will work, since by the time the report is opened, it's already formatted for a specific printer. You can open the report in Design view (hidden), but that's generally a bad idea for several reasons.

If you must check for that before the report opens, often you'll store the name of that printer in a table or file, and then check that table or file using the code I posted earlier. If the name of the printer changes, you then change the name in that table/file, and your code would react to that.
peispudAuthor Commented:
Thank your for your help.

I will try to intercept the message in the OpenReport event in the way that you mentioned.  If that doesn't work,  the I will put the name of the printer in a file or table.

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.