Recordset Question

[Sash]

How can I determine if a recordset has anything in it?

I tried:

Len(rs)
DCount("*", strSQL) -- the SQL used to create the recordset
If rs is not nothing

Thank you,
Sash

 

[Andrew Tapp]

You could try the rs.RecordCount property which returns the number of records
in the recordset.

Depending on how the recordset was opened (e.g.
CurrentDB.OpenRecordset(strSQL, dbOpenSnapshot)) then the .RecordCount may
not hold the correct figure. In this circumstance you would need to move to
the bottom of the recordset then back to the beginning;

If Not rs.EOF Then
rs.MoveLast
End If

If Not rs.BOF Then
rs.MoveFirst
End If

The rs.RecordCount property would then be correct.

Hope this helps.

 

[Marshall Barton]

How can I determine if a recordset has anything in it?

I tried:

Len(rs)
DCount("*", strSQL) -- the SQL used to create the recordset
If rs is not nothing

Len only tell how many characters in a string.

DCount requires the name of a table/query, not an SQL
statement.

A recordset with no records is still a recordset , not
Nothing.

If you open a recordset, then you can use its RecordCount
property. Note that, except for table type recordsets,
RecordCount only tells you how many records have been
processed by DAO. However, if there are any records,
RecordCount is guaranteed to be at least 1. With all that
in mind, the answer to your question is:

If rs.RecordCount > 0 Then
'there is at least 1 record.
Else
'no records
End If


If you are going to loop through all the records in a
recordset, then you don't need to use that kind of check for
an empty recordset. Instead, you can use code like:

Set rs = fb.OpenRecordset(...
Do Until rs.EOF
'process a record
rs.MoveNext
Loop


All that can be found in VBA - Help.