How do I test if a Recordset is empty?

Question

How do I test if a Recordset is empty?

Dim temp_rst1 As Recordset
Dim temp_rst2 As Recordset
            
Set temp_rst1 = db.OpenRecordset("SELECT * FROM ORDER_DATA WHERE SKUS_ORDERED = '" & curSKU1 & "' AND [ORDER] = " & curOrder)
Set temp_rst2 = db.OpenRecordset("SELECT * FROM ORDER_DATA WHERE SKUS_ORDERED = '" & curSKU2 & "' AND [ORDER] = " & curOrder)
            
If IsNull(temp_rst1) Or IsNull(temp_rst2) Then MsgBox "null"

I'm opening up a couple of Recordsets based on a select statement. If there are no records, will IsNull return true?

Lillylillywhite

Answer 1

I would check the "End of File" flag:

If temp_rst1.EOF Or temp_rst2.EOF Then MsgBox "null"

Makeweight

Depending on the way you're opening the recordSet, you may also want to try if it is, at the same time the begin and the end of the recordset (i.e., empty). Then, you'd test recordSet.EOF and recordSet.BOF. – Martens

If a RecordSet returns 1 row of data then would both BOF and EOF be true? I have followed @tom-studee advice below and I check the RecordCount > 0 instead. – Fontanez

@JayKilleen: No. If a recordset contained a single row of data it would open with .BOF = False and .EOF = False. If you did a .MovePrevious from that point, then .BOF = True and .EOF = False. If you did a .MoveNext from the opening point, then .BOF = False and .EOF = True. Make sense? – Makeweight

what if recordset is to update or insert how to check that? same with EOF? – Hartman

@Sam, I'm not really sure what you're asking here. Please clarify or open a new question, optionally with a link to this one. – Makeweight

@Makeweight what i meant if the RS is to update or insert to a table are we going to check EOF? or how to make sure the RS did inserted or updated the table from VBA code. – Hartman

For the record (ha!), after opening a recordset it is never necessary to check recordSet.BOF to see if the recordset is empty. This is in fact cargo cult programming. Checking recordSet.EOF is sufficient. – Hyalo

Answer 2

If temp_rst1.BOF and temp_rst1.EOF then the recordset is empty. This will always be true for an empty recordset, linked or local.

Alber

Answer 3

A simple way is to write it:

Dim rs As Object
Set rs = Me.Recordset.Clone
If Me.Recordset.RecordCount = 0 then 'checks for number of records
   msgbox "There is no records" 
End if

Addiction

Answer 4

If Not temp_rst1 Is Nothing Then ...

Hoecake

That won't work. temp_rst1 will never be nothing in this instance because it gets set two lines earlier. The OP is interested in whether there are records in his recordset. Either of the other two answers accomplish this. – Makeweight

It works correctly with ADO. I am not sure if it works with DAO. I use it extensively with ADO in the following format:Set temp_rst1 = temp_rst1.NextRecordset If Not temp_rst1 Is Nothing Then... – Hoecake

I see. I do very little with ADO, so I'll defer to your expertise there. DAO is generally recommended for use from within MS Access VBA, so I assumed that was the environment the OP was running in. – Makeweight

This answer is very helpful as it explains how to test if a DAO.Recordset variable has been assigned a value at all. – Eloisaeloise

Answer 5

Here, I'm using MS Access 2016 and I check if the recordset field if not null using this:

If (RecordSt.Fields("field_name").Value) Then
' do what you want if there is returned data
Else 
' do what you want if there isn't any data returned by the select

Jasik