Recordset Question

  • Thread starter Thread starter Sash
  • Start date Start date
S

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
 
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.
 
Sash said:
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
Click to expand...


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