Test for no records returned

  • Thread starter Thread starter Brian C
  • Start date Start date
B

Brian C

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
 
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.
 
Back
Top