X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Testing For .eof Without .bof, Access 2016    
post Nov 15 2017, 10:10 PM

Posts: 871
Joined: 3-March 11
From: Sydney, Australia


I have always used the following to test for empty recordset:
Set rs = mydb.OpenRecordset(sQry, dbOpenDynaset)
With rs
    If .EOF Then
        ' do stuff
    End If
End With

I have just read that the test should be:
    If .EOF and .BOF Then

Are there going to be situations where I don't get the expected result if I don't also test for .BOF?

Thanks in advance

EDIT: After searching UA it appears the approach I am using is correct and I could also use:
if rs.recordcount = 0 then
Go to the top of the page
post Nov 16 2017, 12:40 AM

Access Wiki and Forums Moderator
Posts: 72,770
Joined: 19-June 07
From: SunnySandyEggo


It really depends, what is the SQL statement for sQry?

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Nov 16 2017, 04:55 AM

UtterAccess Editor
Posts: 16,272
Joined: 27-June 06
From: England (North East / South Yorks)


I'd say it's primarily about the situation in which you're using this.
For example, in the code you provided, you opened a recordset and then tested it (for content). There's no other way in which the recordset could have achieved EOF status at that point, other than it being empty.
Were this an already open recordset, for example passed to another procedure, then it may have been navigated already. You'd be better off to check both BOF and EOF then to confirm that it is truly empty. (The only want it can ever be both is if it's empty.)
So yes, in this context you had, it's reasonable.

The other option, namely rs.RecordCount = 0, is a slightly different beast.
In itself, yes it's safe... if you're using DAO.
The tendency for testing BOF and EOF is that both DAO and ADO support those properties. But RecordCount's support (to return an actual count) in ADO varies depending upon the cursor type with which you've ended up.
So, back in the early naughties, a lot of code put out there used BOF and EOF so that it was more library independent and invariably supported.

But essentially, for your DAO code, RecordCount is perfectly reliable, as is EOF if used as you did. (It can be argued that there's no reason not to check both BOF and EOF, other than to save a small bit of typing at design time. :-p)


Go to the top of the page
post Nov 16 2017, 07:29 AM

UtterAccess VIP
Posts: 7,502
Joined: 24-May 10
From: Downeast Maine

If you are looping through a recordset you would do something like Do Until rs.EOF, which occurs when you are at the last record and do MoveNext. Of course it will not alse be .BOF in that situation.

When testing a recordset that has just been opened I don't see how it could be .EOF if it is not also .BOF, but as has been mentioned there is very little overhead to testing both, just in case there is a situation you have not anticipated. I tend to test RecordCount = 0, but if not I think I do just .BOF, although I would have to check to be sure.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd July 2018 - 04:47 AM