How to check if the result of a query is zero records

  • Thread starter Thread starter Marco
  • Start date Start date
M

Marco

Hello.

I need to check before print my report that the result of a query is zero
records.

Something like:

if qry1.recordset.count = 0 then
msgbox "there's nothing to show"
end if


Is it possible?

Regards,
Marco
 
Marco,

Dennis' method to use DCOUNT will work. But if there is data, it will take
longer to run because it has to run the query to get the DCOUNT( ) number,
then it has to run it again for the report.

Actually, reports have a NoData event, which fires if the recordset returns
no records. So you could call the report, and in the NoData event, you could
display a message, then cancel the report. If you do this, the line that
called the report will generate an error (I don't remember the number) that
basically says the previous command was cancelled. You need to trap for that
error and allow your code to continue after that.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
The error number is 2501

Here is an example from one of my apps:

In the command button's click event error handler of the form that opens the
report:

If Err.Number = 2501 Then
MsgBox "No Data For " & Me.txtRptYear, vbInformation, "Stars"
Else
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cmdOkay_Click of VBA Document
Form_frmRptProjectListing"
End If
GoTo cmdOkay_Click_Exit

And in the No Data event of the report:

Report_NoData_Error:

If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure Report_NoData of VBA Document
Report_rptProjectListing"
End If
GoTo Report_NoData_Exit
 
Back
Top