vba code to close report if no data

[mikemike]

If my client's data range contains no data, and he opens a report...an obnoxious built in message says "no data". Despite adding cancel=-1 to open report method, it crashes. How do I have it: if a report has no data, I wish the report (with code you advise me) to close the report-how do I do this??

 

[jebry]

Hi!

In the Open routine of the report use this code:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordSet(Me.RecordSource, dbOpenDynaset)

If rst.EOF = True And rst.BOF = True Then
Cancel = -1
End If

hth Jeff Bridgham
bridgham@purdue.edu

 

[JohnLowell]

Use this code to open the report:

On Error GoTo cmdRpterr

DoCmd.OpenReport "Report Name", acViewPreview

Exit Sub

cmdRpterr:

Select Case Err.Number
Case 2501
' report was canceled
Resume Next

Case Else
' Handle Other errors
End Select

Exit Sub

In the report, use this code in the On No Data event on the report:

Private Sub Report_NoData(Cancel As Integer)

On Error GoTo rptnodataerr

MsgBox "No Data Has Been Entered.", vbOKOnly, "Report Reporting Error"

Cancel = True

Exit Sub

rptnodataerr:

' Handle Errors

Exit Sub

End Sub