Cancel print if NoData in report

[Matthew]

I have a report that I have set the "On No Data" value to run the following
macro:
Private Sub Report_NoData(Cancel As Integer)
MsgBox ("Sorry, no records were found with the result you have
selected.")
Cancel = True
End Sub

This somehow is a problem when I use another macro to call the report:
Function PrintReport()
DoCmd.OpenReport "NewCatalog", acViewPreview, "", "", acNormal
DoCmd.PrintOut acPrintAll, , , , 1, True
DoCmd.Close acReport, "NewCatalog", acSaveNo
End Function

The error is:
Run-time error '2501':
The OpenReport action was canceled.

Is there a way to suppress this error, or is there a better way to cancel
the printing of the report if there is no data?

Matthew

 

[Ken Snell]

USing the Report's OnNoData event is a good way to do this cancellation.
However, you need to trap for the error 2501 and you can't do this in a
macro. You must use VBA code to trap for an error.

An alternative way of doing this when using macros is to use a Condition
expression for the macro's OpenReport action (and then you would not use the
report's OnNoData event). Use the DCount function to test if there are
records in the table/query that will be the report's recordsource:

Condition: DCount("*", "ReportRecordsourceName",
"[PrimaryKey]='SomeValue'") > 0
Action: OpenReport


Replace ReportRecordsourceName with the actual name of the report's
recordsource (such as the name of the query), and replace the
[PrimaryKey]='SomeValue' text string with the filtering WHERE statement that
you're using for the report when it's opened.

 

[Matthew]

Thanks, I ended up going with a VBA error handler.

Matthew

USing the Report's OnNoData event is a good way to do this cancellation.
However, you need to trap for the error 2501 and you can't do this in a
macro. You must use VBA code to trap for an error.

An alternative way of doing this when using macros is to use a Condition
expression for the macro's OpenReport action (and then you would not use the
report's OnNoData event). Use the DCount function to test if there are
records in the table/query that will be the report's recordsource:

Condition: DCount("*", "ReportRecordsourceName",
"[PrimaryKey]='SomeValue'") > 0
Action: OpenReport


Replace ReportRecordsourceName with the actual name of the report's
recordsource (such as the name of the query), and replace the
[PrimaryKey]='SomeValue' text string with the filtering WHERE statement that
you're using for the report when it's opened.

--
Ken Snell
<MS ACCESS MVP>

I have a report that I have set the "On No Data" value to run the following
macro:
Private Sub Report_NoData(Cancel As Integer)
MsgBox ("Sorry, no records were found with the result you have
selected.")
Cancel = True
End Sub

This somehow is a problem when I use another macro to call the report:
Function PrintReport()
DoCmd.OpenReport "NewCatalog", acViewPreview, "", "", acNormal
DoCmd.PrintOut acPrintAll, , , , 1, True
DoCmd.Close acReport, "NewCatalog", acSaveNo
End Function

The error is:
Run-time error '2501':
The OpenReport action was canceled.

Is there a way to suppress this error, or is there a better way to cancel
the printing of the report if there is no data?

Matthew