How do I test if a recordSet is empty? isNull?

Question

How can you test if a record set 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 record sets based on a select statement. If there are no records, will IsNull return true?

score 47 · Accepted Answer · answered Jul 22 '11 at 16:32

47

I would check the "End of File" flag:

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

answered Jul 22 '11 at 16:32

mwolfe02

23,787
9
91
161

12

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. – Tiago Cardoso Jul 22 '11 at 16:39
3

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. – Jay Killeen Jun 12 '16 at 23:31
1

@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? – mwolfe02 Jun 13 '16 at 14:31
what if recordset is to update or insert how to check that? same with EOF? – Sam Dec 07 '16 at 16:43
@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. – mwolfe02 Dec 07 '16 at 17:19
@mwolfe02 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. – Sam Dec 07 '16 at 19:56

score 17 · Answer 2 · answered Jul 22 '11 at 16:31

17

RecordCount is what you want to use.

If Not temp_rst1.RecordCount > 0 ...

answered Jul 22 '11 at 16:31

Tom Studee

10,316
4
38
42

Agreed. This allows you to change your processing based on knowing that records returned from the query attempt so that you may alter processing accordingly. +1 – Mohgeroth Jul 22 '11 at 16:34
6

Be aware that RecordCount in this case may not give you an accurate total recordcount. You would need to do a .MoveLast to get an accurate count (and then a .MoveFirst if you want to move through the recordset). That said, it will be greater than 0 if there are *any* records. Bottom line, this answer works but you need to be aware of the idiosyncrasies of .RecordCount. – mwolfe02 Jul 22 '11 at 17:18
11

The DAO Recordcount is NEVER inaccurate for an empty recordset. NEVER. That is, if it's zero, you know it's empty and not going to change. This is 100% reliable and always has been. Also, if it's any number greater than 0, you know it's a non-empty recordset -- 100% reliably. So, there is no reason whatsoever to invoke the overhead of .MoveLast just to find out if a Recordset is empty or not. – David-W-Fenton Jul 24 '11 at 21:31
Wouldn't `If temp_rst1.RecordCount <> 0` be simpler? – David-W-Fenton Jul 24 '11 at 21:33
@David: Yes, I just set up the If that way to match his original code – Tom Studee Jul 24 '11 at 23:06
9

-1: `Recordcount > 0` simply does not work on some ODBC attached tables since it might return -1. `<>0` could work, but it can take a lot of time on an Oracle table having million(s) of rows. – iDevlop Nov 20 '12 at 08:29
Depending on `CursorType` and `CursorLocation` of your Recordset, the RecordCount might or might not be available. – slartidan Sep 08 '20 at 12:20

score 10 · Answer 3 · edited Jul 04 '13 at 15:43

10

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.

edited Jul 04 '13 at 15:43

blmage

4,214
1
23
25

answered Jul 04 '13 at 15:18

BillyBob

101
1
2

score 3 · Answer 4 · edited Jun 16 '15 at 15:24

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

edited Jun 16 '15 at 15:24

Coding Enthusiast

3,865
1
27
50

answered Aug 20 '14 at 10:15

user3959733

31
1

score 1 · Answer 5 · answered Jul 27 '11 at 13:46

1

If Not temp_rst1 Is Nothing Then ...

answered Jul 27 '11 at 13:46

NDizzle

19
1

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. – mwolfe02 Jul 27 '11 at 13:50
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...` – NDizzle Jul 28 '11 at 13:32
1

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. – mwolfe02 Jul 28 '11 at 14:29
This answer is very helpful as it explains how to test if a DAO.Recordset variable has been assigned a value at all. – The Conspiracy Mar 21 '21 at 08:45

score 0 · Answer 6 · edited Aug 16 '21 at 09:18

0

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

edited Aug 16 '21 at 09:18

Adrian Mole

49,934
160
51
83

answered Aug 16 '21 at 09:02

George Leon

1
3

How do I test if a recordSet is empty? isNull?

6 Answers6

Linked