Eliminate message box duplication for the 'On No Data' event w/Rep

[Guest]

A report is generated from user input. However, if there is no data in the
report, I have it set up so that a message box appears saying there is no
data for the query and then the report closes. To determine that the report
has no data I use the report's 'On No Data' event. Since I was not able to
get 'DoDmd.Close acReport' to work under this event, I placed it in the 'On
Page' event.

Everything works fine, Except the message box appears twice. Is there a way
to prevent this. Or perhaps, there is a better approach.

To reiterate, what I want to do is if the report has no data, produce a
message and then when the OK is clicked, close the report.

 

[Brendan Reynolds]

There are two parts to the solution to this problem. First, in the report,
set the Cancel argument to True ...

Private Sub Report_NoData(Cancel As Integer)
Cancel = True
MsgBox "There is currently no data for this report."
End Sub

Second, in the code that attempts to open the report, trap the 'action was
cancelled' error (error number 2501) and make sure your error trapping
option (in the VBA window, Tools, Options, General, Error Trapping) is set
to 'Break on unhandled errors' ...

Private Sub cmdTest_Click()
On Error GoTo ErrorHandler
DoCmd.OpenReport "rptTest", acViewPreview
ExitProcedure:
Exit Sub
ErrorHandler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description
End If
Resume ExitProcedure
End Sub

 

[Guest]

I was way off base in my approach. Your solution worked perfectly. Thanks.

 

[Guest]

On the 'On No Data' you should write

cancel = true
to close the report