No data event function



I have many reports and am trying to use a function to display a message
when there is no data in the report: "Report has no data."

The function seems to work in that if there is data, a report runs with the
data. However, if there is no data, the report does not run but "Report has
no data" does not appear to the user. I'd appreciate suggestions.

Public gfReportHasNoData As Boolean

Public Function PrintPreviewReport(pstrReport As String, pfPreview As
Boolean, pstrWhere As String) As String
'Comments: Print or preview a report and handle errors
'pstrReport Name of report to print
'pfPreview TRUE to preview, FALSE to print
'pstrWhere WHERE clause if any
'Returns: Error string, if any

Dim strError As String

strError = ""

'This becomes true when it's set in the report's NoData event
gfReportHasNoData = False

On Error Resume Next
If pfPreview Then
DoCmd.OpenReport pstrReport, acViewPreview, , pstrWhere
If gfReportHasNoData Then
'This is error 2501 (The OpenReport action was canceled)
'but that could be caused by other reasons in addition to NoData
strError = "Report has no data"
'Set focus to the report and maximize it
DoCmd.SelectObject acReport, pstrReport, False
End If
DoCmd.OpenReport pstrReport, acViewNormal, , pstrWhere
End If

If Err.Number = 2202 Then
strError = "A default printer specified with you installation of
Windows." & vbCrLf & "You must set up a default printer in Windows before you
can view or print any reports."
End If

PrintPreviewReport = strError
End Function

And then the NoData event in the repot:

Private Sub Report_NoData (Cancel As Integer)
Cancel = True
gfReportHasNoData = True
End Sub



You are going about it the hard way. All you need to do is in the No Data
event of the report is something like this:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No Data Matching Selections", vbInformation + vbOKOnly, "BPO By
Cancel = True
End Sub

Then in the error handler of the procedure that opens the report, trap for
the error 2501:

If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure PrintReport of VBA Document Form_frmBPOReports"
End If
GoTo PrintReport_Exit

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads