Test for no records returned

If I run a select query that may return no records how do
I test that there are no records?

I need to do this in code, i.e.code a select statement
followed by the test. Thanks in advance.

try

Dim Rst As DAO.Recordset, strSQL As String
strSQL = "SELECT...."
Set Rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If Rst.BOF And Rst.EOF Then
'code that runs when recordset is empty
End If
Rst.Close
Set Rst = Nothing

or, instead

If DCount("AnyField", "QueryName") 1 Then
'code that runs when query has no records
End If

if your query is the SourceObject of a form, and you're wanting to check for
records on opening the form, you may be able to use RecordsetClone instead
of opening a DAO recordset. but i've never done it that way, so can't give
you specifics. if you figure it out, please post so i can learn too.

hth


"Brian C" wrote in message
...
If I run a select query that may return no records how do
I test that there are no records?

I need to do this in code, i.e.code a select statement
followed by the test. Thanks in advance.

To add to Tina's post, if you want to test a query being used as the Record
Source of a form, you can put the following code in the Open event of the
form:


If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no records to review."
Cancel = True
End If

If there are no records in the query, the form does not open and the message
box is displayed for the user.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


"Brian C" wrote in message
...
If I run a select query that may return no records how do
I test that there are no records?

I need to do this in code, i.e.code a select statement
followed by the test. Thanks in advance.

that's the (easier) one i didn't know - thanks, Cheryl!


"Cheryl Fischer" wrote in message
...
To add to Tina's post, if you want to test a query being used as the
Record
Source of a form, you can put the following code in the Open event of the
form:


If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no records to review."
Cancel = True
End If

If there are no records in the query, the form does not open and the
message
box is displayed for the user.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


"Brian C" wrote in message
...
If I run a select query that may return no records how do
I test that there are no records?

I need to do this in code, i.e.code a select statement
followed by the test. Thanks in advance.