Problems when report has no data

[ccox]

I currently have a form with various reports. I am using the OnClick event with a command button and want to combine code to cancel the report when there is no data. This is what I'm using but I get a runtime error 2501 "the openreport action was cancelled. Any suggestions would be most appreciated. Thanks!

If NoData = True Then
MsgBox "No data to report.", vbOKOnly + vbInformation
Cancel = True
Else
DoCmd.OpenReport stDocName, acPreview
End If

 

[Smart]

I would use the query that the report is based on as follows
in the on click event of the button that runs the report i would do this
(before the report is called)
Dim Nodata as string

Nodata = nz(dlookup("[your column]","[yourreportquery]"),"")

If Nodata = "" then
msgbox "No Data Available etc",vbinformation,"No Data Available"
exit sub
end if

If the query returns a null value (no data) then the event will be exited and the report will not be run

 

[ccox]

That appears to have worked!!! Thank you so much!

 

[Pat Hartman]

The most efficient method is to add error trapping code and simply ignore error 2501 since you know it isn't a problem.

Private Sub cmdPreview_Click()
[COLOR="red"]On Error GoTo Err_cmdPreview_Click[/COLOR]

..your code goes here..
    
[COLOR="Red"]Exit_cmdPreview_Click:
    Exit Sub

Err_cmdPreview_Click:
    If Err.Number = 2501 Then  ' ignore macro action cancelled error
    Else
        MsgBox Err.Number & " - " & Err.Description
    End If
    Resume Exit_cmdPreview_Click[/COLOR]    
End Sub