Bof & eof?

[Gasman]

Hi everyone,

In a lot of snippets of code I see

If Not (rs.EOF And rs.BOF) Then

Where can you get a situation if no records are returned that one or the other is True?
I would have thought no records would set both, and from my little testing I have done, that appears to be the case, so why do people test for both please?

TIA

 

[Frothingslosh]

I think you may be overthinking this.

That code snippet tests to see if both flags are true at the same time, which indicates an empty recordset. If either is false, then there are records.

People test for both to avoid assuming. You don't want to just check BOF because it's way too easy to get false-positives. Just checking EOF is a bad habit to be in just in case you ever check an already-opened recordset and the pointer has been moved to the end, and checking Recordcount could be iffy since some ODBC connections return record counts of -1 instead of 0 for empty sets.

 

[Gasman]

I think you may be overthinking this.

That code snippet tests to see if both flags are true at the same time, which indicates an empty recordset. If either is false, then there are records.

People test for both to avoid assuming. You don't want to just check BOF because it's way too easy to get false-positives. Just checking EOF is a bad habit to be in just in case you ever check an already-opened recordset and the pointer has been moved to the end, and checking Recordcount could be iffy since some ODBC connections return record counts of -1 instead of 0 for empty sets.

Thank you Frothingslosh,

I'll stick with both then.

 

[gemma-the-husky]

if you open a new file, then generally you only need to check eof
I can't think I have ever used both.


open filename
if filename.eof then no records


therefore, with a recordset, say

set rst=currentdb.openrecordset("some query")
while not rst.eof
    do stuff
    rst.movenext
wend

 

[pbaldy]

I don't think I've ever checked for BOF. Perhaps I've avoided potential problems with already opened recordsets with logical program flow.

 

[Frothingslosh]

Honestly? I've only encountered a false positive with .EOF with one application, and I inherited that one from someone else who specialized in illogical program flow. Since then, I've always used the (.BOF and .EOF) formatting just from pure paranoia.

I'm rather certain, as I generally run the check immediately after opening the recordset, that I've never actually needed the .BOF check since then, but I figure a fraction of a millisecond is little enough cycle time that it's not a big deal. Perhaps I should rethink that now that I'm dealing with tables with millions and tens of millions of records.

 

[pbaldy]

I won't pretend to know what's happening "under the covers", but I'd be surprised if the added BOF check would be a performance drag. If that's your habit, I wouldn't necessarily change it. I was simply saying that I haven't used it and haven't run into a problem. Nothing wrong with a "belt and suspenders" approach either.

 

[jdraw]

I agree with Paul re belts and suspenders- and if it's working for you or if it's just habit --then carry on.

My understanding,( and I couldn't find confirmation when searching just now) is that when you do OpenRecordset, it will position you at the first record by default.
So, if there is/are no records, you'll be at EOF.

I don't recall checking for BOF, but always use Not rs.EOF.....

 

[pbaldy]

My understanding,( and I couldn't find confirmation when searching just now) is that when you do OpenRecordset, it will position you at the first record by default.

I don't have a cite either, but I've always done loops the way Dave showed and it always starts at the first record.

 

[Gasman]

if you open a new file, then generally you only need to check eof
I can't think I have ever used both.


open filename
if filename.eof then no records


therefore, with a recordset, say

set rst=currentdb.openrecordset("some query")
while not rst.eof
    do stuff
    rst.movenext
wend

Thank you Dave,

That was my thinking, but was curious as to why a lot of people seemed to use both.
I must admit I was thinking only of the situation that you have described.

It is good to know that some experts here do not use it. Matter of style I suppose. I have slowly changed how I write code as I learn more

eg rst.Fields("EmployeeID") to rst!EmployeeID

just less to type.

 

[JHB]

I've nor used both together.

 

[stopher]

I think you may be overthinking this.

That code snippet tests to see if both flags are true at the same time, which indicates an empty recordset. If either is false, then there are records.

People test for both to avoid assuming. You don't want to just check BOF because it's way too easy to get false-positives. Just checking EOF is a bad habit to be in just in case you ever check an already-opened recordset and the pointer has been moved to the end, and checking Recordcount could be iffy since some ODBC connections return record counts of -1 instead of 0 for empty sets.

What Frothingslosh says makes sense and I am intrigued when we might do this. Consider:

Public Sub test()
    
    Dim db As Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblSomeData")
    
    Do While Not rs.EOF
        Debug.Print rs(1)
        'maybe do some stuff with the recordset
        rs.MoveNext
    Loop
    
    'our recordset pointer is now at eof
    Debug.Print archiveData(rs) & " records archived"
    
End Sub


Public Function archiveData(rs As Recordset) As Integer
    i = 0
    
    Do While Not rs.EOF
        'maybe do something else with the recordset
        i = i + 1
        rs.MoveNext
    Loop
    archiveData = i
End Function

The count will be zero because a recordset has been passed to the function where the pointer is at eof.

So in the function we need to add rs.movefirst before the loop. But this will crash if there is no records (empty rs). So we must test for both eof and bof using an if statement first. The other benefit of this is that we can deal with the empty recordset case in the else clause.

So the function becomes:

Public Function archiveData(rs As Recordset) As Integer
    If Not (rs.BOF And rs.EOF) Then
        rs.MoveFirst
        i = 0
        Do While Not rs.EOF
            'maybe do something else with the recordset
            i = i + 1
            rs.MoveNext
        Loop
    Else
        'maybe do womething else when there are no records
    End If

    archiveData = i
End Function

Note that if we had just use IF NOT rs.eof, then this would always be false in the above example because the recordset passed is already at eof.

Regards
Chris

 

[gemma-the-husky]

@stopher.

I have missed managing already open recordsets correctly, in the past, but it's another thing that once you are familiar with it, you don't get caught again.

in your example though, it's easy to reposition the recordset cursor before the second process. eg.

Public Sub test()
    
    Dim db As Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblSomeData")
    
[COLOR="Red"]    if rs.eof then
         msgbox "No records to manage"
         rs.close
         exit sub
    end if[/COLOR]

    Do While Not rs.EOF
        Debug.Print rs(1)
        'maybe do some stuff with the recordset
        rs.MoveNext
    Loop
    
    'our recordset pointer is now at eof
     
[COLOR="red"]    'since we know there are items then simply this before calling the next sub

    rs.movefirst[/COLOR]
    Debug.Print archiveData(rs) & " records archived"
    
End Sub

 

[stopher]

in your example though, it's easy to reposition the recordset cursor before the second process.

Agreed it's easy but imho not good coding.

If you code the way you suggest then you are relying on everyone who ever uses the function to know that they must movefirst. A good function should deal with this. Would you write a function that took a string as an input without testing for an empty string? The point is the person who writes the function can't possible rely on the user of the function meeting such a specific requirement. So the function coder should deal with it.

Of course if you are coding both sides then you may say that you can do it as you suggest. But I would say that's what leads to problematic code because you lose site of what goes on where e.g. why have I put a movefirst in my test code?? Oh yeah, it was because my function can't deal with it.

 

[jdraw]

You could close the recordset when finished with first task/usage. Then, call the archiveData function.
But I agree, the design of the archiveData function must set up the data appropriately to ensure the "archive" actually archives what is intended.

 

[Gasman]

But this will crash if there is no records (empty rs).

I would ask why you are calling a function and passing the recordset when the recordset might be empty?

 

[stopher]

I would ask why you are calling a function and passing the recordset when the recordset might be empty?

One example might be that you are a tour operator. The function generates tickets. So you loop through all your tours and pass a recordset of all the people booked on the given tour. Of course some tours might not have any bookings. That's fine because the function sees the empty recordset and maybe sends an email to the tours controller to cancel the tour.

I appreciate there are other ways to do this and as mentioned before I cannot think where I have used "not (bof and eof)". But I can think of scenarios where it might be used.

 

[Gasman]

I asked that question as I have this scenario at present
Main sub
Call CalcHours
Call CalcCommission
Call CalcBonus
End Sub

and there is no point calling CalcBonus if there are no commission records, so I test for that before the call.

I take your point though.

Thank you.