VBA MsgBox Print .Echo Freeze

[Jester0001]

The code below is for a form, and is triggered by the OnExit event of the subform. A MsgBox asks the user if they want to print a report. If they clicked yes, then the report opens in print preview. Next, the print dialog box appears. Here's where a problem occurs:

If the print job is executed, then everything is okay. However, if the print job is canceled from the print dialog, then the application "freezes" because the screen repainting isn't turned back on. I could easily remove the screen repainting, but then the user has to see all of the background steps to print the report. What can be done to still have the screen repainting disabled at the beginning, and then enabled at the end, even if the user cancels the print job from the print dialog box?

Thank you in advance

Private Sub subSubform_Exit(Cancel As Integer)
'To show a Yes/No message box when the user exits the subform to ask if they want to print a report.
If MsgBox("Do you want to print a report?, 4 + 32, "Print Report?") = 6 Then

'Enter below what to do if they click 'Yes' (start printing)

 On Error GoTo ErrorHandler
    
    'To turn off screen repainting
    Application.Echo False
    
    DoCmd.OpenReport "rptReport", acViewPreview
    
    'Opens print dialog for current screen (report in this case):
    DoCmd.RunCommand acCmdPrint
    
'<<<<<<<<Code stops here if Print Dialog is canceled>>>>>>>>>>>>>

    'To close the report after being sent to the printer
    DoCmd.Close acReport, "rptReport"
    
    'To turn on screen repainting
    Application.Echo True

ErrorHandler:
    If Err.Number <> 0 And Err.Number <> 2501 Then
        MsgBox "Error: " & Err.Number & vbNewLine & Err.Description
        Exit Sub
    End If
'Enter below what to do if they click 'No' (exit Sub)
Else: 'MsgBox "They Clicked No"
    Exit Sub
End If




End Sub

[Jester0001]

I believe I found a solution. When the print dialog is canceled, error 2501 is returned. Thus, the error handling in the previous code is set to only return errors that are not 0 and 2501 (...And Err.Number <> 2501). Thus, I added a little bit more error handling to the code to resume next on error 2501:

ErrorHandler:
    Select Case Err
      Case 2501
         Resume Next
      Case Else

   End Select
    If Err.Number <> 0 And Err.Number <> 2501 Then
        MsgBox "Error: " & Err.Number & vbNewLine & Err.Description
        Exit Sub
    End If
'Enter below what to do if they click 'No' (exit Sub)
Else: 'MsgBox "They Clicked No"
    Exit Sub
End If

End Sub

[ssanfu]

Well, first off, you are missing a closing double quote after the question mark in this line:

If MsgBox("Do you want to print a report?", 4 + 32, "Print Report?") = 6 Then

It is much easier to read (IMO) if you use VBA constants:

   If MsgBox("Do you want to print a report?", vbYesNo + vbQuestion, "Print Report?") = vbYes Then

The on error statement is in the wrong place. it should be the line after
"Private Sub subSubform_Exit(Cancel As Integer)"

Then you have the "ELSE ... END IF" in the wrong place. It should be before the "ExitHere:" label. BTW, putting a colon ( : ) after the "Else" made it a label - shouldn't put a colon after the Else.

The big question is why you set screen updating to FALSE, then open the report in preview mode??? You can't see the preview, so why open it??


Why don't you just do this?

Private Sub subSubform_Exit(Cancel As Integer)
      On Error GoTo ErrorHandler

'To show a Yes/No message box when the user exits the subform to ask if they want to print a report.
   If MsgBox("Do you want to print a report?", vbYesNo + vbQuestion, "Print Report?") = vbYes Then

      'Enter below what to do if they click 'Yes' (start printing)

      'Opens print dialog for current screen (report in this case):
      DoCmd.RunCommand acCmdPrint

   End If

ExitHere:
   Exit Sub

ErrorHandler:
   If Err.Number <> 0 And Err.Number <> 2501 Then
      MsgBox "Error: " & Err.Number & vbNewLine & Err.Description
   End If
   Resume ExitHere

End Sub

[Jester0001]

ssanfu,

You asked very good questions! Thank you.

First of all, this mess of code is what I get from copying/pasting stuff until it works (I'm full of shame!)

The big question is why you set screen updating to FALSE, then open the report in preview mode??? You can't see the preview, so why open it??

The code is run from a Form, and I want to print a Report. I disabled screen updating so that the user wouldn't see the report being opened, printed, then closed. The format of the Form is different than the format of the Report (is this bad design?) I tried your code, but it prints the Form, not the Report. Is there a way to print the Report from the Form without opening the Report, and still having the print dialog box open so the user can make selections if desired?

Thanks!

[ssanfu]

Should have read my own code posting.. Sorry...


Try this:

Private Sub subSubform_Exit(Cancel As Integer)
      On Error GoTo ErrorHandler

'To show a Yes/No message box when the user exits the subform to ask if they want to print a report.
   If MsgBox("Do you want to print a report?", vbYesNo + vbQuestion, "Print Report?") = vbYes Then

      'Enter below what to do if they click 'Yes' (start printing)

      'Opens print dialog for report
      DoCmd.OpenReport "rptReport"

   End If

ExitHere:
   Exit Sub

ErrorHandler:
   If Err.Number <> 0 And Err.Number <> 2501 Then
      MsgBox "Error: " & Err.Number & vbNewLine & Err.Description
   End If
   Resume ExitHere

End Sub

From Help:

acViewNormal prints the report immediately. If you leave this argument blank, the default constant (acViewNormal) is assumed.

So you can use:
DoCmd.OpenReport "rptReport",acViewNormal
or
DoCmd.OpenReport "rptReport"

Both will result in the print dialog box being displayed to print the report.