On No Data Error

[Paul H]

I was trying to add a On No Data event to my report with this snippet of code.

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data for this Trans Type and Date Range."
    Cancel = True
End Sub

The report is called by this standard command which worked just fine till I tried to add the On No Data Event.

DoCmd.OpenReport strReportName, acViewPreview

Specifically I get this error (see picture below) on the Cancel = True line.
I have many other reports that use this method. Any ideas what's causing this?

[isladogs]

The error is expected behaviour in the calling form event which tried to open the report.
You've cancelled the report opening which triggers error 2501

Add code to the form event to handle that error

Err_Handler:   
    'err 2501 - report no data
    If Err = 2501 Then Exit Sub

[Minty]

I've always found this "feature" of access frustrating - you should be able to supress the error if the call is cancelled, or at least have a flag you could set to not raise that specific error.

[isladogs]

The form error handler I used does suppress the error.

[Minty]

The form error handler I used does suppress the error.

Colin, I get that - but why make the on no data function return the action cancelled error, that's the annoyance.
It's almost as easy (and a similar amount of code) to check for records yourself and simply not open the report in the first place.

Think I need more coffee

[isladogs]

I have at times used that approach to count the records in the calling form. Something like...

Dim lngCnt As Long  strSQL As String
strSQL = "your report record source"

 lngCnt=Count("*", strSQL)

If lngCnt>0 Then
   DoCmd.OpenReport "ReportName, acViewPreview
Else
   MsgBox "No data for the report"
End If

End Sub

As you say, a similar amount of code overall...but it has to be customised for each report record source so perhaps takes a bit more thought /time to do.

Anyway, having got involved in a fairly fruitless discussion about DateValue yesterday, i certainly don't intend to argue the case for one no data method over the other.

[Minty]

As you say, a similar amount of code overall...but it has to be customised for each report record source so perhaps takes a bit more thought /time to do.

Anyway, having got involved in a fairly fruitless discussion about DateValue yesterday, i certainly don't intend to argue the case for one no data method over the other.

Never fruitless here Colin, I like an apple.
And I'm not disagreeing with you, I just think it's an oversight from MS to make you have to deal with an error caused by the very function you are using to try and prevent an error in the first place.
(if that makes sense)