conditional check if recordset null

[kgcrowther]

I would like to check if a recordset is null. The following simple code makes sense to me, but doesn't work (note the if statement, the rest is just context):

SQL = "SELECT blah blah..."
Set rsMembership = db.OpenRecordset(SQL)
If rsMembership Then
some operations ...
End If

The SQL statement should return either one tuple or nothing. I've stepped through and the SQL works fine. However, what is the syntax for checking the "nullity" of the recordset in the conditional statement?

I've also tried...

If rsMembership notnull then ...
If rsMembership not null then ...
If rsMembership <> null then ...

It seems so simple, but I can't find the right words to look up the correct syntax in any search engines. In the past I have worked around this with:
If rsMembership.recordcount > 0 then ...

But, I would like to know how to ask if it is null. Thanks for any help.

 

[RuralGuy]

Try:
If rsMembership.RecordCount > 0 Then

 

[ajetrumpet]

In the past I have worked around this with:
If rsMembership.recordcount > 0 then ...

But, I would like to know how to ask if it is null.

Try:
If rsMembership.RecordCount > 0 Then

To me, you've just answered your own question, along with RuralGuy doing it a second time. If you haven't, I would be interested in hearing what you mean by NULL? To me anyway, NULL = 0 records

 

[pbaldy]

As an alternative:

If rsMembership.EOF Then
'no records returned

 

[YevS]

A recordset cannot be NULL. It will either have records or not.

What you could do is check if a "field" within a recordset is null.

If isnull(rs!Fieldname) then

msgbox "This recordset is null"

end if

 

[pbaldy]

Have you tested that YevS? I did, and it didn't work. The proper way to test for an empty recordset is either the record count method RG posted or testing for EOF.

 

[WayneRyan]

KG,

We seem to have lost you over the weekend/holiday, but here's my 2 cents worth.

I don't use "rst.RecordCount" unless I do a rst.MoveLast then a rst.MoveFirst
Whether it's an ADO thing or a DAO thing, *sometimes* the rst.RecordCount = 0
when there really are records.

I always use:

If Not rst.EOF and Not rst.BOF Then
   MsgBox("There are records.")
Else
   MsgBox("There are no records.")
End If

One thing is certain though, the RecordSet will NEVER be Null and even the presence
of a Null field means nothing.

Wayne

 

[kgcrowther]

Great discussion about recordset properties

Thank you for the great discussion about recordset properties. I believe that I'll stick with the method recommended by RuralGuy, which seems to be the easiest - and is working fine.

I gained a lot of insight from all the discussion in this thread. THANKS!

 

[RuralGuy]

You may wish to change the code to:

If rsMembership.RecordCount [COLOR="Red"]<>[/COLOR] 0 Then

...since there are a number of situations where there are records but -1 is returned. Access at least guarantees a NonZero value if there are records.

 

[The_Doc_Man]

Since Yves capitalized his NULL in the statement that a recordset cannot be NULL, I will agree - with a qualified agreement. But from the set theory viewpoint, an empty recordset really IS also null. But Access just doesn't report it that way.

The test for NULL on a recordset variable does not mean what it might seem to mean. I think a recordset VARIABLE really CAN be NULL in the formal sense - if it isn't instantiated yet. When you close a recordset, you should also reset the pointer with a

Set MyRecSetVar = Nothing

But if a recordset variale IS properly instantiated, it will have the correct record count only if you try to .MOVEFIRST and .MOVELAST (or vice-versa) to force Access to traverse the internal structure of the table.

When RG is talking about returning -1 for a recordcount, I think that only can occur when the underlying table or query involves something external to the current DB file. For all local tables, the .MOVEFIRST, .MOVELAST operation should correctly populate the properties of the recordset - including its record count.

Base advice: Read up on recordset properties and look at examples to get a better feel for what you can - and cannot -do with them.

 

[jbrumbau]

Hello,

I searched everywhere for the answer to this too and finally found something, so I'm posting on here to help everyone else out. I was trying to pass a recordset as an optional parameter to a function. Here is what I did to get it to work:

Sub setupTable(..., Optional ByRef rsExist As Recordset = Nothing, Optional ByRef rsNew As Recordset = Nothing)
    ...
        If Not rsExist Is Nothing Then
            ...
        End If
    ...
End Sub