ErrorHandler behind command button event property in form fails to work.

[Frank]

BlankI have a form with a command button on it. The button opens a report.
What I want to do is this:

If the report has no records in it, I want a pop up message to display "This
report has no records." Then automatically close after the OK button on the
message box is clicked.

I created the following code and placed in the events property of the
commands button to process the above action>


On Error GoTo ErrorHandler
Dim stDocName As String

stDocName = "My Report"
OpenReport stDocName, acPreview

ExitHere:
Exit Sub
ErrorHandler:
If Err.Number = 2501 Then
MsgBox "No records found."
True
Else
MsgBox Err.Description, , "Error# " & Err.Number
End If
Resume ExitHere

End sub



When running the above, the report opens but nothing else happens.

How do I fix this?

Any assistance is deeply appreciated.

 

[Jeff Conrad]

You're close, but not quite there.

Open the Report in Design View (not the form).
Put this code in the Report's On No Data event:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ErrorPoint

MsgBox "This report has no records to display." & vbNewLine _
& "This report will now be closed.", vbOKOnly + vbInformation, _
"No Records Found"
Cancel = True

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox Err.Description, , "Error# " & Err.Number
Resume ExitPoint

End Sub

Now change your command button to this:

Private Sub cmdMyCommandButton_Click()
On Error GoTo ErrorHandler

Dim stDocName As String

stDocName = "My Report"
DoCmd.OpenReport stDocName, acPreview

ExitHere:
Exit Sub

ErrorHandler:
If Err.Number <> 2501 Then
MsgBox Err.Description, , "Error# " & Err.Number
End If
Resume ExitHere

End Sub

Now your message will be displayed, but no Access error messages will pop up.

 

[Albert D. Kallal]

You only get a error message in the report if you "cancel" the report. (so,
you have to use the reports on-no data even, and set cancel = true. Only
when you set cancel = true will a error be raised.

That means in the reports on-no data, you must put code in that to cancel
the report.

Since you must put code in the report..then you might as well move the
message box to the report also (that way, you can open the report from more
then one location..and not have to write a bunch of code. It also means that
special, or more revenant messages will be placed with the report

So, in your reports on-no data event, put the following:

Private Sub Report_NoData(Cancel As Integer)

MsgBox "no reocrds found",vbExclamation,"no data"
Cancel = True

End Sub

Then, change your code to:
stDocName = "My Report"
on error resume next
OpenReport stDocName, acPreview

If you want, you can keep your error code as is, but check the error for a
cancel and DO NOTHING.

If Err.Number <> 2501 Then
MsgBox Err.Description, , "Error# " & Err.Number
End If

and, last but not least. You could keep your code as is..and simply NOT put
the msgbox in the reprots on-nodata event.

 

[Frank]

Thanks so much all. As you can guess, I am very new to coding. Your
comments were most excellent. Tried code and everything works perfectly.

Thanks once again for your timely and professional assistance.

Frank

 

[Jeff Conrad]

You're welcome, glad to help.