Why extremely occasionally will one of bof/eof be true for a new non-empty recordset

Question

 set recordsetname = databasename.openrecordset(SQLString)
    if recordsetname.bof <> true and recordsetname.eof <> true then
    'do something
    end if

2 questions :

1. the above test can evaluate to false incorrectly but only extremely rarely (I've had one lurking in my code and it failed today, I believe for the first time in 5 years of daily use-that's how I found it). Why very occasionally will one of bof/eof be true for a non-empty recordset. It seems so rare that I wonder why it occurs at all.

2. Is this a foolproof replacement:

   if recordsetname.bof <> true or recordsetname.eof <> true then
   

Edit to add details of code :

Customers have orders, each order begins with a BeginOrder item and end with an EndOrder item and in between are the items in the order.

The SQL is:

' ids are autoincrement long integers '
SQLString = "select * from Orders where type = OrderBegin or type = OrderEnd"           

Dim OrderOpen as Boolean
OrderOpen = False

Set rs = db.Openrecordset(SQLString)
If rs.bof <> True And rs.eof <> True Then
    myrec.movelast
    If rs.fields("type").value = BeginOrder Then
         OrderOpen = True
    End If
End If

If OrderOpen F False Then
    'code here to add new BeginOrder Item to Orders table '
End If

ShowOrderHistory 'displays the customer's Order history '

In this case which looks this this

BeginOrder
Item a
Item b
...
Item n
EndOrder

BeginOrder
Item a
Item b
...
Item n
EndOrder

BeginOrder
Item a
item b
...
Item m

BeginOrder     <----should not be there as previous order still open

Answer 1

The pattern I have always used is:

Set rs = db.OpenRecordset(...)

Do while Not rs.EOF

    ' Rest of your code here.

    rs.MoveNext
Loop

I have never seen this fail (yet!). This is described here: How to: Detect the Limits of a DAO Recordset

As an aside, Allen Browne's VBA Traps: Working with Recordsets might be of interest.

Answer 2

The documentation clearly states that, if you open a Recordset that has no records:

• BOF will be true
• EOF will be true
• RecordCount will be 0

For a non-empty Recordset, neither BOF and EOF are true until you move beyond the first or last record.

Could it be that, from time to time, someone else could have added/deleted a record to one of the tables in the recordset you're just opening and change the resultset?
It could be the result of a race condition.

Rather than use BOF or EOF, you can test on Recordcount: it's always 0 if the recordset is empty.
If the recordset is not empty, it will usually return 1 right after the recordset has been open; Recordcount isn't an expensive operation in that case.
The only way to really return the actual number of records is to issue a MoveLast before calling Recordcount to force all records to be loaded.

Usually, if I need to iterate through a resultset in read-only fashion:

Dim db as DAO.Database
Dim rs as DAO.RecordSet

Set db = CurrentDB()
Set rs = db.OpenRecordSet("...", dbOpenForwardOnly)
If Not (rs Is Nothing) Then
    With rs
       Do While Not .EOF
            ' Do stuff '
            .MoveNext
       Loop
       .Close
    End With
    Set rs = Nothing
End If
Set db = Nothing

If I don't need to iterate through records but just test if anything was returned:

Set rs = db.OpenRecordSet("...", dbOpenForwardOnly)
If Not (rs Is Nothing) Then
    With rs
       If .RecordCount > 0 Then
          ' We have a result '
       Else
          ' Empty resultset '
       End If
       .Close
    End With
    Set rs = Nothing
End If
Set db = Nothing

It's pretty defensive and you have to adapt to your circumstances, but it works correctly every time.

Regarding your 2nd question, testing (BOF Or EOF) after opening the recordset should be more foolproof than the And version, although I'd use Recordcount myself.

Edit following your revised question:

From the bit of code you added to your question, I see a couple of issues, the main one being that your SQL Statement is missing and ORDER BY clause.
The problem is that you are expecting the resultset to be in the Begin Order followed by End Order sequence but your SQL Statement doesn't guarantee you that.
In most cases, since you're using an autoincrement as ID, the database engine will return the data in that natural order, but there is no guarantee that:

• It's always going to happen that way
• That the original data was saved in the expected sequence, resulting in IDs that are in the 'wrong' order.

So, whenever you have expectations about the sequence of the resultset, you must explicitly order it.

I would also refactor this bit of code:

' ids are autoincrement long integers '
SQLString = "select * from Orders where type = OrderBegin or type = OrderEnd"           

Dim OrderOpen as Boolean
OrderOpen = False

Set rs = db.Openrecordset(SQLString)
If rs.bof <> True And rs.eof <> True Then
   myrec.movelast
    If rs.fields("type").value = BeginOrder Then
        OrderOpen = True
    End If
End If

Into a separate function similar to:

' Returns true if the given CustID has a Open Order, '
' false if they are all closed.'
Public Function IsOrderOpen(CustID as Long) As Boolean
    Dim result as Boolean
    result = False

    Dim sql as String
    ' Here I assume that the Orders table has a OrderDateTime field that '
    ' allows us to sort the order in the proper chronological sequence '
    ' To avoid loading the complete recordset, we sort the results in a way '
    ' that will return the last used order type as the first record.'
    sql = sql & "SELECT Type " 
    sql = sql & "FROM Orders "
    sql = sql & "WHERE ((type = OrderBegin) OR (type = OrderEnd)) "
    sql = sql & "      AND (CustID=" & CustID & ")"
    sql = sql & "ORDER BY OrderDateTime DESC, Type DESC;"

    Dim db as DAO.Database
    Dim rs as DAO.Recordset
    Set db = CurrentDB()
    Set rs = db.Openrecordset(sql, dbOpenForwardOnly)

    If Not (rs Is Nothing) Then
        If rs.RecordCount > 0 Then
            result = (rs!type = BeginOrder)
        End If
        rs.Close
    End If

    Set rs = Nothing
    Set db = Nothing

    IsOrderOpen = result
End Function

This would make the whole thing a bit more robust.

Answer 3

This is DAO, right? I'm more an ADO man myself but IIRC there are circumstances (dynaset?) where you need to navigate EOF in order for the final number of rows to be assessed. Could it be in this state that EOF is true, BOF is false (because it hasn't been navigated yet) but as soon as BOF is navigated it is true (obviously) and EOF remains true. Presumably the initial state when zero rows are expected is supposed to be instantaneous but a once-in-five-years freak timing incident means you captured it in a really early initial state?

Answer 4

@Renaud Bompuis's answer is quite good. Let me emphasize the point that the DAO Recordcount is never zero for a non-empty recordset, and that is the only thing I ever test in determining if a recordset has returned records. I use .EOF for looping through the records, but don't start stepping through the records until I've already tested if there are records returned.

Answer 5

Here's a possible solution

It could be that your form or module has gotten corrupted. Export/Import the affected module or form, or try the /decompile option. In my case a query was coming back empty when it shouldn't have, but I think the core problem could be similar.

Answer 6

I occasionally come across the exact same bug in access (had it today in Access 2007 linked to a sql server back end) where the statement

if rst.bof and rst.eof

evaluates to false despite rst representing an empty recordset. When it happened, VBA started and the debugger in the immediate pane showed that, indeed rst.bof was true and rst.eof was true, so it seems to happen for a millisecond and then is corrected, but after one has tested the logic.