BOF EOF
By Richard Rost
20 days ago
Why Check Both for Empty Recordsets? In this lesson, we will explain how BOF and EOF work in DAO recordsets and why both values must be True to confirm an empty recordset. We will walk through how current-record position changes with MoveNext and MovePrevious, why EOF alone does not mean no records were returned, and how to safely test and process recordsets after opening them. We will also discuss RecordCount behavior and a few navigation considerations after deleting records. Scott from Lansing, Michigan (a Platinum Member) asks: I'm checking whether a customer recordset returned anything before I loop thru the results. Sometimes my code says there are no records even though I know it found customers, especially after I've moved through the list. Why does that happen, and what's the reliable way to tell if the query really returned nothing? MembersIn the extended cut, we will build a recordset explorer with buttons to move first, previous, next, and last. We will create a reusable HasRecords function, test recordset navigation behavior, and use a form-level recordset variable to keep the recordset open while working with an unbound form. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, DAO recordset BOF EOF, empty recordset check, VBA recordset navigation, DAO MoveNext MovePrevious, CurrentDb OpenRecordset, VBA EOF error, DAO RecordCount MoveLast, VBA no current record, DAO recordset cleanup
Subscribe to BOF EOF
Get notifications when this page is updated
More InformationTranscript Have you ever opened a recordset, checked EOF, and assumed there were no records? Only to have your VBA code do something weird and unexpected?
B-O-F and EOF seem simple enough, but they confuse a lot of Access developers.
Welcome to another TechHelp video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
Today, we're going to make sense of B-O-F and EOF in DAO recordsets. More importantly, you'll understand why checking both of them tells you whether a recordset actually returned no records, while checking just one can lead you astray. Love that word, astray.
We'll look at what Access means by a current record, what happens as you move through a recordset, and why that little B-O-F and EOF test isn't just a magic VBA spell you have to memorize.
Today's question comes from Scott in Lansing, Michigan, one of my Platinum members.
Scott says, "I'm checking whether a customer recordset returned anything before I loop through the results. Sometimes my code says there are no records, even though I know it found customers, especially after I've moved through the list. Why does that happen, and what's the reliable way to tell if the query really returns something? Or nothing."
Well, let's take a look.
All right, a lot of developers have seen a line of code that says if rs.BOF and rs.EOF, and then a lot of people just memorize that as, "This means there are no records." And that's correct, but it really doesn't explain anything.
So why do we need both properties? Why not just check EOF? And what's B-O-F anyway?
Well, in this lesson, we're going to make sense of them visually, and once you understand where the current record is and where the two boundaries are, you won't have to memorize this strange Access incantation. It'll just be obvious.
We're going to use a simple analogy first, and then we're going to jump into a live Access demonstration and watch these values change for real.
All right, so let's first define a recordset. A recordset in DAO or ADO, for that matter, is an object in VBA that represents rows of data. It could be all the customers in your CustomerT table or the results of a query.
Think of it as a row of index cards. Or like my analogy shows, a row of blocks. Little kids playing with blocks.
Now, each block represents one record. And a recordset, if it has records, has a current record. And in this analogy, that'll be this little guy. That's the current record.
And this is the record that the recordset is currently looking at. It's called the bookmark. That's the bookmark.
And we can move that current position with commands like MoveNext and MovePrevious. Move back, move forward.
And now here's the key idea. B-O-F and E-O-F are not records. They're boundary positions just outside the records.
B-O-F means beginning of file, and E-O-F means end of file. Why file? That goes back to the old file-handling days. It's a long story.
Anyways, in practical Access terms, B-O-F means the position before the first record, and E-O-F means the position after the last record.
Now let's also think of those B-O-F and E-O-F record markers as if they're squeezing the blocks together and they're spring-loaded. That's how I like to imagine them.
And we've got five records in our tray, one through five. And on the left, B-O-F has a spring that pushes inward until it touches the first record. And on the right, E-O-F has its own spring pushing inward, and it touches the last record.
And this little transparent window in the middle is our bookmark. That's our current record. And right now, it's sitting on record three.
The actual records are between B-O-F and E-O-F, as long as there is at least one record in the recordset. Those two boundary markers can't occupy the same space.
And again, this is a visual representation. It's not the literal internal design of DAO, but it captures the behavior that you need to understand. Records create space between the beginning boundary and the ending boundary.
Now, as we call MoveNext, the bookmark moves from record three to record four. And that's all navigation normally does. It changes which record is the current record.
And B-O-F hasn't moved, and E-O-F hasn't moved. There are still five records there.
And if we call MovePrevious, the bookmark moves back a record.
And if we keep using MoveNext, eventually, we'll move from the last record to the position after it. And at that point, if the bookmark is sitting over here, E-O-F becomes true.
And if we keep moving backwards past the first record, B-O-F becomes true.
And this is where beginners often get confused. EOF equals True does not mean that the recordset has no records. It simply may mean that you've walked past the records. You've walked through all the records and taken one more step past the end.
Now let's shrink our recordset down to just one record. B-O-F is pressing against the left side, and E-O-F is pressing on the right side.
There's only one record, but there's still a block in here. And the bookmark is still on that one block. B-O-F is False and E-O-F is False.
Now, if you're sitting here and you MoveNext, you're moving on to E-O-F, and now E-O-F becomes True.
And if you go previous and then previous again, now B-O-F will be True.
Now here's the tricky moment. Here's the aha moment. If there are no records in the tray, no customers matched your query or whatever, no invoices are overdue, whatever you're looking for, nothing came back.
It's an empty recordset. There's no first record for B-O-F to sit next to, and there's no last record for E-O-F to sit next to. And there's no current record for our bookmark to point to.
So in our picture, the two spring-loaded markers come together because there's nothing between them.
And that's why the test works that says if rs.BOF and rs.EOF. If they're both True, if both flags are True at the same time, that only happens if the recordset has no records.
B-O-F says there's no current record because we're at the beginning side. And E-O-F says there's no current record because we're at the ending side.
If both are True together, there's no row. There's no block between them. There's no row that Access can make current. There's no record there.
So this is the analogy that I like to keep in my brain when I'm thinking of recordsets.
Now, EOF by itself being True doesn't mean the query returned nothing. It can mean I was on the last record, and then I used MoveNext one more time. There may have been one record, 10 records, 10,000 records before you got there.
And likewise, B-O-F by itself means that you are before the first record. But if both of them are true, it's an empty recordset.
But you could get there by calling MovePrevious while you're already on a record.
Now, one small practical detail: after opening a normal, non-empty recordset, Access is generally positioned on the first record.
So when you open a recordset, if there are records, the bookmark will be sitting on record one. Just like this guy, even if it's only one record, it'll be sitting on record one.
And if it's sitting on record one, both flags are False. B-O-F and E-O-F will both be False.
And that makes the empty recordset test especially useful immediately after OpenRecordset.
And so here's the practical use. Suppose you open a recordset based on a table or a query or an SQL statement. And maybe you find some customers. And sometimes that's going to return records, and sometimes it returns none.
So after opening the recordset, you do an if rs.BOF and rs.EOF. And if the markers are together, if there's no blocks in the middle, then you got no records found. Otherwise, do whatever you got to do, loop through them or whatever.
We'll see an example of this in just a minute.
Now, before we get to the code, before we get to the examples, this is a developer-level video. So if you've never done any VBA programming before, go watch this video. It'll teach you everything you need to know in about 20 minutes.
And we'll be using my status box to display stuff. I like it better than MessageBox.
You should know what recordsets are and how to work with them. And you can even see from the screenshot here in this example. I open up a recordset, and then I go right into a loop.
But fortunately, with this example, it says while not rs.EOF. As long as you're checking that before the loop, if there are no records, it'll just skip on by it.
But if you want to open the recordset and do stuff with the first record, this isn't a reliable test.
We're going to use a tiny little bit of SQL, so make sure you watch this video.
And you should know how to use a While loop. I love While loops. I know there's lots of different kinds of loops. While loops are my favorite, so I use them a lot. That's the one I use.
These are all free videos, by the way. They're on my website, they're on my YouTube channel. Go check them out and come on back.
All right, just a short demo today. Here I am in my TechHelp free template. You can grab a copy of this off my website if you want to, but you can do this with any database.
Now in here, I got a Customer table with a bunch of customers in it. And what we're going to do is we're going to make a little recordset that's going to open it and then tell us if we're sitting on EOF or B-O-F.
Let's right-click and go to Design View. Let's hijack this Hello World button. We're going to use this guy. Build Event.
And we bring this down here so I can work with it.
And we're right here in the Hello World button. It just says Hello World, and that's it. But we're going to make it do some other stuff.
Let's start by declaring a recordset. So, Dim rs As Recordset, and we'll need an SQL string to put our SQL string in.
SQL is going to be equal to Select * From CustomerT. That says, give me all the fields from all the records in the Customer table.
Then we'll say Set rs = CurrentDb.OpenRecordset(SQL), just like that.
And then, when you're done, don't forget rs.Close. And if you set it, you got to forget it. So, Set rs = Nothing.
If you set it, you got to forget it. It's important. It's so important, I put it on a mousepad.
Now inside here, all I want to do is I want to open up that recordset and just take a look. I want to say, if rs.EOF, then Status EOF. Just tell me if you're sitting at the EOF marker.
And if rs.BOF, then Status BOF.
That's all I want.
And if we're neither of those things, that means we're on a record. So if Not rs.EOF And rs.BOF, then Status, the ID that we're on is CustomerID. We got to do rs!CustomerID.
If it's an empty recordset, rs.EOF and rs.BOF will both be True. Keep this in your brain hole. If rs.BOF and rs.EOF are True, that means you've got no records.
If both of them are True, that's the only reliable way to test to see if you have no records.
So if rs.BOF and rs.EOF, if this is True, that means we got no records. So we're just going to say if not that. So if this is False, then we've got a record. Show me what it is.
That's all we're going to do with this example right now.
Save it. Debug Compile once in a while. That's also on a mousepad.
We're going to close it. Close it. Open it. And click the button.
And I get ID equals one. Why is that? Because I've got records.
So what happens is the recordset opens, and recordsets always open to the first record. And unless you have an ORDER BY in your SQL statement, you cannot reliably tell which one it is.
It's usually the order that they appear in the table, but not always. Don't rely on it unless you use an ORDER BY statement.
Let's change our SQL statement a little bit. Let's say WHERE 1 = 0. That's a trick to return an empty recordset because one is never equal to zero.
Save it. And now let's click the button.
Boom. Look at that. B-O-F and E-O-F are both True. See? If it's True, then Status that. So that means we have an empty recordset.
Let's change it a little bit more. WHERE CustomerID = 1. This should return a recordset with one record.
And then do that. See? Now we got just one in there.
Now I do one more thing. I'm going to open the recordset, and then as soon as the recordset is open, I'm going to do rs.MovePrevious. I'm going to move back a record. And then we'll see what happens.
Save it. Click.
Oh, no current record. What happened? No current record. Debug.
Well, it came down to here. Hmm. Let's see what happened here.
Ah, it opened up, and immediately it said B-O-F. Notice there's no EOF up here. This is B-O-F, because it came down to here. It checked EOF. No, I'm not sitting on the EOF.
It came down here. Oh, look at that. B-O-F is True.
And then it came here, and we're checking to see if rs.EOF and rs.BOF are both True, which they're not, because B-O-F is still False. Excuse me, EOF is still False. We're not at the end marker. We're just on the beginning marker.
We opened up a recordset with one record, and then we went back a record. So we're sitting on the B-O-F marker right now.
But since both of these aren't True, this tried to run in. You can't get the CustomerID because there is none. You're sitting on the B-O-F marker.
So we can reword this just slightly. We can say if Not rs.EOF And Not rs.BOF, then tell me who it is.
Save it. And now I can click, and it'll say B-O-F because we're sitting on the B-O-F marker.
Now it might be a more reliable test if you want. If you come up here, this is where you can put the check here and see if you have records at all.
You could say if rs.EOF and rs.BOF, then Status, no records, and then Exit.
Oh, we got to close it. Actually, we can do a GoTo here. That's what I usually do. We go down to the bottom and close everything, because you've already opened the recordset.
You don't want to exit the subroutine and leave the recordset open because you've set it. Don't forget to forget it.
So we're going to say GoTo. I call it CleanExit most of the time.
And if what I do is put a GoTo spot down here, I say CleanExit: That becomes a GoTo jump spot.
I got a whole separate video on GoTo. It's really handy. Or you can just do a whole other if-else block, but this is cleaner.
So you open the recordset, check to see if there are records. If there are no records, just jump right down here, close the recordset, destroy it, and then you're all set.
And I'm also going to add one more thing up here. We're going to say StatusBox = blank because it's easier to read than this thing keeps adding to itself.
So now when I click on it, click, I just see the B-O-F. Because I opened up a recordset with one record. It passes this test. RemovePrevious, and all I see is B-O-F because we're sitting on the B-O-F marker.
What if I put in here if CustomerID is greater than one?
Well, now if I click the button, I still see B-O-F. Because it started off on two. It started off on record two, but record one doesn't exist in this recordset. It's two and up, but two is still the first record in this SQL set.
Let's get rid of the MovePrevious. Let's see what happens.
Now we're on ID two. See? This is fascinating stuff. I could go on for hours with this stuff.
And I am going to go on a little longer. We got an extended cut for the members today.
We're going to build a little recordset explorer, I call it. We're going to do just like we did. We're going to do little buttons to go left and right, to the first and to the last.
We're going to make a reusable HasRecords function. We're going to see how different things work with moving around in recordsets.
We're going to make a form-level recordset variable. That way, you can keep it alive while you play with the buttons. You don't have to keep opening and closing it. You just open it once, move around, and then close it.
This is how unbound forms can still work with records.
We're going to cover this in the extended cut for the members. Silver members get access to all of my extended cut videos, and Gold members can download these databases that I build in the TechHelp videos. And everybody gets some free training classes in the big happy family.
So click that blue Join button for more information.
And hey, while I got your attention, hit that Like and Subscribe. Do it. Do it right now.
Before we go, a few quick details to help you avoid some future head-scratchers.
Remember, when you open a non-empty recordset, Access normally places you on the first record. You don't start on B-O-F. You start on record one, like I mentioned earlier.
This is why you can normally begin processing right away. If you start here and then MoveNext, MoveNext, and you say While Not EOF, that works fine until you get to the EOF marker. Then you drop out of your loop.
RecordCount is not a good empty test until you move to the end of the recordset. Keep that in mind, too.
I see a lot of people opening recordsets and they try to issue a RecordCount command. That doesn't work until you go to the end of the recordset.
You have to issue an rs.MoveLast command first, then RecordCount works.
And finally, if your recordset deletes records, or even if you're deleting records outside the recordset but you still got a recordset open, which is what I do, I don't usually delete records inside a recordset.
I'll navigate through the recordset, but then I'll issue Delete with an SQL statement, so it's not the recordset itself deleting the records. It's a long story. Maybe I'll make another video on that someday.
But if your code deletes records anywhere, especially the last record, don't assume that the flags instantly tell the whole story without repositioning.
Recreate your position again after a delete. In other words, MoveFirst, MoveLast. Don't just delete a record and then assume that the EOF will slide over, because sometimes it doesn't.
All right, let's wrap it up.
B-O-F means current position is before the first record. EOF means the current position is after the last record.
Either one by itself can happen in a perfectly good recordset after you navigate beyond one end. Do not interpret EOF alone as there were no records.
But when B-O-F and EOF together are both True, then there are no records. The recordset is empty.
So whenever you open a recordset and you need to know whether there's anything in it, use if rs.BOF and rs.EOF, then you got no records.
So now you know not only what to type, but why.
I'm all about the why. Why do we do things this way? Why does this work? Not just memorize this code.
You'll come to me to learn stuff, not just, "Here's a bunch of code, type it in."
So hopefully, that's why I like the little blocks analogy. I love that. And so does my little penguin. He loves it too.
And once you understand why this stuff works the way it does, VBA and programming in general get a whole lot less mysterious.
So there you go. That's B-O-F, E-O-F, how they work, and why you check both of them for empty recordsets.
And that's going to do it for your TechHelp video for today. I hope you learned something.
Live long and prosper, my friends. I'll see you next time, and members, I'll see you in the extended cut. Intro In this lesson, we will explain how BOF and EOF work in DAO recordsets and why both values must be True to confirm an empty recordset. We will walk through how current-record position changes with MoveNext and MovePrevious, why EOF alone does not mean no records were returned, and how to safely test and process recordsets after opening them. We will also discuss RecordCount behavior and a few navigation considerations after deleting records. Quiz Q1. What does BOF mean in a DAO recordset?
A. The current position is before the first record
B. The current position is on the first record
C. The recordset has been closed
D. The recordset contains only one record
Q2. What does EOF mean in a DAO recordset?
A. The current position is on the last record
B. The current position is after the last record
C. The recordset contains no fields
D. The first record has been deleted
Q3. What is the reliable test for determining whether a DAO recordset is empty?
A. If rs.EOF Then
B. If rs.BOF Then
C. If rs.BOF And rs.EOF Then
D. If rs.RecordCount = 0 Then
Q4. Why is checking rs.EOF by itself not a reliable empty-recordset test?
A. EOF is always False after OpenRecordset
B. EOF can be True after moving past the last record in a non-empty recordset
C. EOF only works with table-type recordsets
D. EOF becomes True only when a record is deleted
Q5. If a recordset has records and is currently positioned on a valid record, what are the usual BOF and EOF values?
A. BOF is True and EOF is True
B. BOF is True and EOF is False
C. BOF is False and EOF is True
D. BOF is False and EOF is False
Q6. What happens when you call MoveNext while positioned on the last record?
A. The current position moves after the last record and EOF becomes True
B. The current position remains on the last record
C. The recordset automatically closes
D. BOF and EOF both become True
Q7. What happens when you call MovePrevious while positioned on the first record?
A. The recordset moves to the last record
B. The current position moves before the first record and BOF becomes True
C. EOF becomes True
D. The first record is deleted
Q8. Why can code fail if it tries to read rs!CustomerID while BOF is True?
A. CustomerID is not available until MoveLast is called
B. DAO cannot read fields from a recordset opened with SQL
C. There is no current record while positioned at BOF
D. BOF changes all field values to Null
Q9. Which condition should be used before reading fields from a recordset to make sure it is positioned on a valid current record?
A. If Not rs.EOF And Not rs.BOF Then
B. If rs.EOF Or rs.BOF Then
C. If rs.RecordCount > 0 Then
D. If rs.MoveNext Then
Q10. After opening a normal non-empty DAO recordset, where is the current position normally located?
A. Before the first record
B. On the first record
C. After the last record
D. On no record until MoveFirst is called
Q11. Why is RecordCount not generally a good initial test for an empty recordset?
A. RecordCount only works for records with numeric IDs
B. RecordCount is always zero for query recordsets
C. RecordCount may not reflect all records until the recordset has been moved to the end
D. RecordCount causes EOF to become True
Q12. What should code generally do after opening a recordset and finding that both BOF and EOF are True?
A. Call MoveNext until EOF becomes False
B. Process the first record anyway
C. Jump to cleanup code that closes and releases the recordset
D. Call MovePrevious to create a current record
Q13. Why should SQL include an ORDER BY clause when code depends on which record appears first?
A. Without ORDER BY, the first returned record is not reliably defined
B. ORDER BY is required to use BOF and EOF
C. ORDER BY makes RecordCount immediately accurate
D. ORDER BY prevents MoveNext from reaching EOF
Q14. After deleting records, especially the last record, what is a safer practice before relying on BOF or EOF status?
A. Assume EOF will always update automatically
B. Reposition the recordset, such as with MoveFirst or MoveLast
C. Set BOF and EOF to False manually
D. Open a second copy of the same recordset
Answers: 1-A; 2-B; 3-C; 4-B; 5-D; 6-A; 7-B; 8-C; 9-A; 10-B; 11-C; 12-C; 13-A; 14-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. Summary Today's video from Access Learning Zone explains how BOF and EOF work in DAO recordsets, why both properties matter when determining whether a recordset is empty, and why checking only one of them can produce misleading results.
Many Access developers learn that they should test BOF and EOF when opening a recordset, but they memorize the rule without understanding why it works. Once you understand what these properties represent, recordset navigation becomes much less mysterious.
A DAO recordset is a VBA object that represents a collection of rows from a table, query, or SQL statement. For example, I might open a recordset containing every customer in the CustomerT table, or I might open one containing only customers that meet certain criteria.
A recordset has a current position. When the recordset contains data, it normally has a current record. This is the record that VBA is currently looking at, and it is the record whose field values you can read or modify.
You can move through a recordset using methods such as MoveNext and MovePrevious. These methods change the current record position.
BOF and EOF are not actual records. They are positions outside the available records.
BOF means Beginning Of File. In practical Access terms, it means the current position is before the first record.
EOF means End Of File. In Access terms, it means the current position is after the last record.
The word "file" comes from older database and file-handling terminology, but the important point is that BOF and EOF describe the boundaries around the data in a recordset.
Imagine a recordset as a row of blocks. Each block represents one record. If there are five records, there are five blocks in the row. The current record is like a marker sitting on one of those blocks.
BOF is positioned just before the first block. EOF is positioned just after the last block.
If the current record is somewhere in the middle of the row, both BOF and EOF are False. You are currently positioned on a valid record.
If you move forward from the last record one more time, you move beyond the available records. At that point, EOF becomes True.
That does not mean the recordset never contained records. It only means that you moved past the last record.
Likewise, if you move backward from the first record one more time, you move before the available records. At that point, BOF becomes True.
Again, this does not mean the recordset is empty. It may contain one record, ten records, or thousands of records. It simply means that your current position is before the first available record.
The important situation is when both BOF and EOF are True at the same time.
If a recordset contains no records at all, there is no first record and no last record. There is also no valid current record. In that situation, BOF and EOF are both True.
That is why the standard empty-recordset test checks both properties together. If both BOF and EOF are True, the recordset returned no rows.
Checking EOF by itself is not reliable because EOF can become True after you have navigated through a perfectly valid recordset and moved past the last record.
Checking BOF by itself is also not reliable because BOF can become True after you move backward from the first record.
The combined BOF and EOF test is the reliable way to determine whether a recordset contains no records.
Immediately after opening a normal non-empty DAO recordset, Access generally positions the current record on the first available row. At that point, BOF is False and EOF is False.
This is why you can usually begin processing a recordset right away after opening it. If it contains records, you are normally already sitting on the first one.
For example, if I open a recordset based on the CustomerT table, the recordset will normally be positioned on its first customer record. Unless I specifically include an ORDER BY clause in the SQL statement, I should not assume which customer will be first. It may appear to use table order, but that order is not guaranteed unless I explicitly sort the recordset.
If I open a recordset that intentionally returns no rows, such as one based on criteria that can never be true, BOF and EOF will both be True immediately after opening it.
If I open a recordset containing a single record, both BOF and EOF will initially be False because the current position is on that one record.
However, if I then use MovePrevious, BOF becomes True because I have moved before that record. EOF remains False because the recordset still has a valid ending boundary.
At that point, I cannot read a field value from the recordset because there is no current record. Attempting to refer to a field such as CustomerID while positioned at BOF or EOF will generate a "No current record" error.
This is an important distinction. The recordset still contains data, but my current position is not on a valid row.
If I need to read field values, I should make sure that I am not at either boundary. In other words, I should only process fields when BOF is False and EOF is False.
A good general sequence is to open the recordset, test whether it is empty by checking whether BOF and EOF are both True, and then cleanly exit if no records were returned. If records do exist, I can process them or begin looping through them.
When using recordsets, always remember to close the recordset when you are finished and set the object variable to Nothing. Leaving recordsets open unnecessarily can lead to resource problems and other unexpected behavior.
A common looping pattern is to process records while EOF is False. This works well because a non-empty recordset begins on the first record, and each MoveNext advances through the records. Once the code moves past the last record, EOF becomes True and the loop stops.
An empty recordset also works safely with this type of loop because EOF is already True. The loop simply does not execute.
However, if you need to do something with the first record immediately after opening the recordset, you should not assume there is a current record until you have checked whether the recordset is empty.
Another point to remember is that RecordCount is not a reliable empty-recordset test immediately after opening many types of recordsets. Depending on the recordset type, Access may not know the full record count until you move through the records or move to the last record.
If you need an accurate RecordCount, you may need to use MoveLast first. But if all you need to know is whether any records were returned, checking BOF and EOF is usually the better and more efficient approach.
Be especially careful when deleting records. If your code deletes records from a recordset, or deletes records using separate SQL statements while a recordset remains open, do not assume that the current position and boundary flags will instantly behave exactly as you expect.
Deleting the current record, especially the last record, can leave the recordset position in a state that requires repositioning. In those cases, it is often wise to move to a known location again, such as MoveFirst or MoveLast, before continuing to process records.
The key points are simple:
BOF means the current position is before the first record.
EOF means the current position is after the last record.
Either property can be True in a recordset that contains valid records if you navigate beyond one end.
When BOF and EOF are both True, the recordset is empty and no records were returned.
Also, in today's Extended Cut, we will build a reusable recordset explorer with buttons for moving to the first, previous, next, and last records. I will create a reusable HasRecords function, demonstrate additional recordset navigation behavior, and show how to use a form-level recordset variable to keep a recordset open while working with an unbound form.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends. Topic List DAO recordset BOF and EOF properties
Current record and recordset boundaries
Why BOF and EOF both mean no records
Why EOF alone does not mean an empty recordset
Why BOF alone does not mean an empty recordset
Opening a DAO recordset with OpenRecordset
Checking for empty recordsets after opening
Navigating with MoveNext and MovePrevious
Avoiding no current record errors
Processing records only when on a valid record
Using BOF and EOF after recordset navigation
RecordCount limitations in DAO recordsets Article A recordset is an object that represents a collection of rows returned from a table, query, or SQL statement. When you work with a recordset in Access VBA, it has a current position. If the recordset contains records, that current position identifies the record you are currently reading or editing.
BOF and EOF describe positions outside the actual records in the recordset. BOF means Beginning Of File, which in practical terms means the current position is before the first record. EOF means End Of File, meaning the current position is after the last record.
Neither BOF nor EOF is itself a record. They are boundary markers. Imagine the records as blocks in a row. BOF is just to the left of the first block, and EOF is just to the right of the last block. As long as there is at least one record, there is space between those two boundaries.
When you open a normal non-empty DAO recordset, Access normally places the current position on the first record. At that point, BOF is False and EOF is False because you are sitting on a valid record.
If you move forward through the recordset and then move forward once more after the last record, EOF becomes True. That does not mean the recordset was empty. It only means you have moved beyond the final record. There may have been one record or thousands of records before reaching that position.
The same idea applies in reverse. If you move backward from the first record, BOF becomes True. This also does not mean the recordset has no records. It means you have moved before the first available record.
A recordset is truly empty only when both BOF and EOF are True at the same time. This happens because there is no first record, no last record, and no current record. There are no rows between the two boundaries.
That is why the reliable empty-recordset test checks both properties together. Immediately after opening a recordset, determine whether BOF and EOF are both True. If they are, the query returned no records. If they are not both True, the recordset contains at least one record.
Checking EOF alone can lead to incorrect conclusions. For example, a recordset may contain several customers, but after a loop processes all of them, the current position will be at EOF. Looking only at EOF at that point might make it appear that no customers were found, when in reality the code simply reached the end of a valid list.
Similarly, checking BOF alone is not an empty-recordset test. A recordset can contain records while the current position is before the first one because code moved backward too far.
When processing records in a loop, the usual approach is to begin on the first record and continue while EOF is False. This works because a non-empty recordset normally begins on a valid record, and the loop ends after moving past the last one. If the recordset is empty, EOF is already True, so the loop does not run.
However, if your code needs to work with the current record directly, such as reading a field before entering a loop, first confirm that the recordset is not empty. Attempting to read a field while positioned at BOF or EOF causes a "No current record" error because there is no active row to read.
RecordCount is also not the best way to determine whether a recordset is empty. Depending on the recordset type and how it is opened, Access may not know the full count until the recordset has been fully traversed or moved to the last record. BOF and EOF provide a more direct and reliable answer to the simple question of whether any rows were returned.
After deleting records, be careful about assuming the current position and boundary flags automatically reflect exactly what you expect. If a deletion affects the current record or the final record in the set, reposition the recordset as needed before continuing. Moving to the first or last record, when appropriate, can help establish a known position.
The key idea is simple: BOF means you are before the first record, and EOF means you are after the last record. Either condition can occur in a recordset that contains valid data. When both BOF and EOF are True together, the recordset contains no records at all. Primary Topics DAO recordsets, BOF property, EOF property, current record position, empty recordset detection, MoveNext and MovePrevious navigation, safe record access Secondary Topics OpenRecordset behavior, SQL filtering, RecordCount limitations, recordset cleanup, deleting records and repositioning, While Not EOF loops |
