Error handling code

[Rmaster2022]

I have the following code for a “On No Data” event:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "Nothing to print.", vbCritical, "No data"
Cancel = True
End Sub

On all of my reports, if there is no data, when I close the message box, I get another window that says: “The Open Report action was canceled,” with an OK button to close.

However, when I close the message box on one of my reports, I get the runtime error 2501 with the end, debug, and help buttons. One difference on this report is that it is based on a parameter query that asks for a year. I’m afraid this window will confuse the users.

I tried this code but got the same response.

'------------------------------------------------------------

' Macro1

'------------------------------------------------------------

Function Macro1()

On Error GoTo Macro1_Err

Beep

MsgBox "There is no data to print.", vbCritical, "No data."
DoCmd.CancelEvent

Macro1_Exit:
Exit Function

Macro1_Err:
MsgBox Error$

Resume Macro1_Exit

End Function

As you can probably tell, this is a conversion from a macro. I am very much a beginner in VBA, so I probably need the full code.

Also, I’m aware that I should have some error handling code included in the code I copied that I have for all the other reports. What should that code be?

 

[theDBguy]

Try changing this area:

Macro1_Err:
If err.Number <> 2501 Then
    MsgBox Error$
End If
Resume Macro1_Exit

Hope that helps...

 

[gemma-the-husky]

The 2501 error is in the code that called the report, and is triggered because the report did not open. Of course the report may not open because of a design issue, so you need to consider whether you can safely dismiss the 2501 error..

 

[Rmaster2022]

I still get this window after closing the first message box.

This is the code I have with your changes.

Function Macro1()
On Error GoTo Macro1_Err

Beep

MsgBox "There is no data to print.", vbCritical, "No data."
DoCmd.CancelEvent

Macro1_Exit:
Exit Function
Macro1_Err:

If Err.Number <> 2501 Then
MsgBox Error$
End If

Resume Macro1_Exit

End Function

 

[theDBguy]

I still get this window after closing the first message box.

View attachment 102449

This is the code I have with your changes.

Function Macro1()
On Error GoTo Macro1_Err

Beep

MsgBox "There is no data to print.", vbCritical, "No data."
DoCmd.CancelEvent

Macro1_Exit:
Exit Function
Macro1_Err:

If Err.Number <> 2501 Then
MsgBox Error$
End If

Resume Macro1_Exit

End Function

Oh sorry, as @gemma-the-husky said, you'll need that change in the code that has the OpenReport code in it.

 

[The_Doc_Man]

In the specific case where you are going to cancel the report, try this

DoCmd.SetWarnings False
DoCmd.CancelEvent
DoCmd.SetWarnings True

 

[arnelgp]

are you running the Report from a Macro:

 

[arnelgp]

if you are opening it in a sub:

On Error GoTo Err_Handler
DoCmd.OpenReport "yourReportHere", acViewReport

Exit_Here:
Exit Sub

Err_Handler:
    If Err <> 2501 Then
        MsgBox Err.Number & ": " & Err.Description
    End If
    Resume Exit_Here

 

[strive4peace]

@Rmaster2022

if you trace the origin of the error message, you'll see it isn't coming from the report itself (Report_NoData looks ok). When you open the report, instead of testing for a specific error number if there's a problem, an easy way is to skip any error message. If the report doesn't open properly, the user probably already knows why ...

Here is VBA that could run on the Click event of a command button to open a report:

   On Error Resume Next  'skip error if there is one
   DoCmd.OpenReport "Reportname", acViewPreview

 

[gemma-the-husky]

Thinking about it again, if there's no data, there's already a 2051 error

So you would normally suppress the error in this way

if err<>2051 then
   msgbox "Error opening from report" & vbcrlf & "Error " & err & "  Desc: " & err.description
end if

However, if the report/form fails to open for a reason other than no data, then you still get error 2051 - so if it fails to open because of another error then this message would be suppressed. You could get the "onNoData" event to set a sentinel public variable somewhere, so that you can distinguish between 2051 because of no data, or 2051 for another reason. It's often a matter of making sure your app is bulletproof, so that the form/report will definitely open, unless there is no data.

 

[Rmaster2022]

if you are opening it in a sub:

On Error GoTo Err_Handler
DoCmd.OpenReport "yourReportHere", acViewReport

Exit_Here:
Exit Sub

Err_Handler:
    If Err <> 2501 Then
        MsgBox Err.Number & ": " & Err.Description
    End If
    Resume Exit_Here

This worked! Thank you. I feel so dumb--I should have checked the Open Report code!