How to return cleanly from report with no data to report menu

[collizz]

I need to identify where a report selected from a listbox menu of reports has no data to display, then display a "No Records" message before returning to the reports menu list.
When a report is selected from the menu, I call the report with the code:

OpenReport strReportName, acViewPreview

In the called report's NoData event, I use the code:

MsgBox "No Records for Report", vbExclamation, "No Records"
Cancel = True

to (hopefully) bring the user cleanly back to the reports menu.

If the report is run at the report level (not via menu), this works fine. However, when I select the report from the reports menu and it encounters zero records, then it works differently.
If I leave the "Cancel = True" statement in, then I get a "Run-time Error 2501" message, telling me the OpenReport action was cancelled that there is a DoCmd conflict.
If I remove the "Cancel = True" line, I have to approve the messagebox twice, then the report is generated with a "#Error" field on the page.

Appreciate your help.

 

[Pat Hartman]

You need to add error trapping to the event that runs the OpenReport method. You can just ignore the 2501 error.

Select Case Err.Number
   Case 2501 
   Case Else
    Msgbox Err.Number & "-" & Err.Description, vbOKOnly
End Select

 

[Smart]

You could do a dlookup using the query that your report is based on

Out the dlookup prior to calling the report then check if the returned value is null if it is null output your no data message end exit the sub and you will automatically be returned to the report menu

Dim answer as string

answer = nz(dlookup("Querycolumnname,"queryname"),"")

if answer = "" then
msgbox "No Data Available",vbinformation,"No Data"
exit sub
end if

 

[collizz]

OK, guys, thanks. Onward and upward ...

 

[collizz]

Pat,
Sorry to be dim, but I added the Select Case statement as you suggested in the event that calls the report (click on listbox item) but I can't trap the error in time. The 2501 message comes up before the Select Case statement kicks in. What am I doing wrong here? I tried to add an OnError event to the form but no luck there either.
Code extract follows.

Private Sub lbx_Reports_DblClick(Cancel As Integer)
Dim strRepName1 As String
Dim strRepName2 As String
Dim strReportName As String
strRepName1 = Forms!frm_ReportsMenu!lbx_Reports
strRepName2 = Forms!frm_ReportsMenu!lbx_Reports.Column(1)
strReportName = strRepName1 & " - " & strRepName2
DoCmd.OpenReport strReportName, acViewPreview
Select Case Err.Number
Case 2501
Case Else
MsgBox Err.Number & "-" & Err.Description, vbOKOnly
End Select
End Sub

 

[Jibbadiah]

I use the following:

Dim dblAnythingThere As Double

dblAnythingThere = DCount("[Field]", "ReportQueryName")

If dblAnythingThere = 0 Then
MsgBox "No records found.", vbExclamation
GoTo PROC_EXIT:
Else:
DoCmd.OpenReport "rptReport", acViewPreview
End If

 

[collizz]

I appreciate I can check query results, etc, but I would rather go with Pat's suggestion, as I have perhaps 30 reports going through this selection process and I don't fancy coding and maintaining for every one of them!
A simple, generic invocation as I have now will work fine, if I can just trap the error immediately after invoking the OpenReport method. It comes up with the 2501 message before it can drop through to the error trapping, however.
If I define the error handling outside of the OnClick event, would it work any differently? How do I do this?

 

[collizz]

How to return cleanly, etc

Thanks to you all for the suggestions - it works fine now - after I put the error trapping in the right place! Sorry to waste your time, guys.