One of those little bits of Access VBA that people tend to memorize without really understanding is the empty recordset test: If rs.BOF And rs.EOF Then. It works, yes, but if you only check EOF, you can easily convince yourself that a query returned no records when in fact you simply walked off the end of a perfectly good recordset. Been there, done that, got the mousepad.
The key is to stop thinking of BOF and EOF as records. They are not records. They are positions just outside the records in a recordset. Once you understand where those positions are and how navigation affects them, the famous BOF/EOF test stops being a magic incantation and starts making perfect sense.
A DAO recordset is simply an object that represents rows returned from a table, query, or SQL statement. It might contain every customer in your Customer table, all overdue invoices, or the results of a filtered search.
Imagine the records as a row of index cards. If the recordset contains records, one of those cards is the current record. That is the record your code is currently sitting on and the one whose fields you can read with something like rs!CustomerID.
BOF means "Beginning Of File," which is old terminology that has stuck around since the early days of file handling. In practical Access terms, BOF means the current position is before the first record.
EOF means "End Of File." It means the current position is after the last record.
Neither BOF nor EOF is a real record. They are boundary positions. Think of BOF as being just to the left of the first record and EOF as being just to the right of the last record.
If you open a normal, non-empty DAO recordset, Access generally positions it on the first record automatically. At that point, both rs.BOF and rs.EOF are False, because you are sitting on an actual record.
If you call MoveNext repeatedly, eventually you move beyond the last record. At that point, EOF becomes True. That does not mean the recordset was empty. It may mean that your recordset had one record, ten records, or ten thousand records, and your code simply took one step too many.
Likewise, if you are on the first record and call MovePrevious, you move before the first record. BOF becomes True. The recordset can still contain plenty of records. You are just positioned outside of them.
This is why checking only EOF can lead you astray. Consider a loop that moves through all records with MoveNext. When the loop ends, EOF is True. If you then test only EOF and decide that means "no records were found," you are wrong. Records were found and processed. You are merely parked past the end of the list.
The same thing applies to BOF by itself. BOF being True does not prove that there are no records. It may simply mean you called MovePrevious while positioned on the first record.
The important case is when both BOF and EOF are True at the same time. That condition tells you there is no current record because there are no records at all. There is no first record to sit before, no last record to sit after, and no actual row that Access can make current.
That is why the standard empty recordset test is If rs.BOF And rs.EOF Then. Both flags being True means the recordset is empty.
This test is especially useful immediately after opening a recordset. For example, you might open a recordset based on a query that searches for customers matching some condition. Sometimes it will find rows, and sometimes it will not. Right after OpenRecordset, check BOF and EOF together. If both are True, there is nothing to process. Otherwise, you can work with the current record or start looping through the results.
If you need to read a field from the current record, be a little more careful. You need to make sure you are not positioned at either boundary. In other words, the safe condition is that BOF is False and EOF is False. If either one is True, there is no current record available to read.
This distinction matters because an empty-recordset check and a current-record check are related, but not quite the same thing. rs.BOF And rs.EOF tells you whether the recordset has no records at all. Not rs.BOF And Not rs.EOF tells you that you are currently positioned on a valid record.
For example, suppose a query returns exactly one customer. When you first open that recordset, both BOF and EOF are False because you are sitting on that one customer. If you call MoveNext, EOF becomes True. If you instead call MovePrevious, BOF becomes True. The recordset still has one record in it, but you are no longer sitting on it.
Another common beginner trap is using RecordCount to determine whether a recordset is empty. That can be unreliable with certain types of recordsets because DAO may not know the full count until you have navigated through the records, often by moving to the last record first. If all you want to know is whether at least one row was returned, BOF and EOF are the cleaner test.
Also remember that recordset position matters after deletes. If your code deletes records, especially the last record, do not assume the BOF and EOF flags will always behave exactly as you expect without repositioning the recordset. Move to an appropriate location again, such as the first or last record, before making assumptions about where you are.
And, of course, when you are finished with a DAO recordset, close it and set the object variable to Nothing. Open objects should be cleaned up properly. If you set it, do not forget to forget it.
So the short version is this: BOF means you are before the first record. EOF means you are after the last record. Either one can happen in a recordset that contains valid data. But when both are True, the recordset is empty.
Once you visualize BOF and EOF as boundaries rather than records, a lot of recordset navigation becomes much less mysterious. For the complete walkthrough, including live Access demonstrations of moving before and after records, watch the embedded video.
Live long and prosper,
RR
No comments:
Post a Comment