No Current Record
By Richard Rost
2 days ago
Error 3021 Causes and Fixes In this lesson, we will explain Microsoft Access Runtime Error 3021, "No Current Record," and why it can occur even when a recordset contains records. We will discuss BOF, EOF, recordset navigation, empty recordsets, FindFirst and NoMatch checks, and using the Immediate Window to identify where your code lost its current record. Marcel from Spokane, Washington (a Platinum Member) asks: I'm opening a Recordset to look up customer information, and sometimes Access gives me Run-time Error 3021, "No current record," when I try to use a field. What confuses me is that I know the Recordset can have records in it. Why does this happen, and how can I prevent it? PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, Runtime Error 3021, No Current Record, recordset BOF EOF, empty recordset, recordset navigation, DAO recordset, FindFirst NoMatch, MoveNext EOF, MovePrevious BOF, VBA Immediate Window, recordset debugging, VBA error handling
More InformationTranscript Have you ever had Access throw a "no current record" error at you, even though you know darn well that your recordset has records in it? Yep, it can do that. Your code can lose its place, and suddenly there's no record there to work with.
Welcome to another TechHelp video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
Today, we're going to talk about Microsoft Access Runtime Error 3021, "No Current Record." You'll see why this error doesn't always mean that your query came back empty, what it really tells you about the current position in your recordset, and how to track down the line of code that got you there so you can prevent it from happening again.
Today's question comes from Marcel in Spokane, Washington, one of my Platinum members. He says, "I'm opening a recordset to look up some customer information, and sometimes Access gives me a Runtime Error 3021, 'No Current Record,' when I try to use a field. What confuses me is that I know the recordset can have records in it. Why does this happen, and how can I prevent it?"
Yes, this error can happen even when the records are there. This usually means your code has lost its place, so let's take a look at what's really going on.
All right, so before we get started, this is a developer-level video. If you have not yet watched my Intro to VBA class and you want to learn some VBA, go watch this. It'll get you started and teach you everything you need to know in about 20 minutes to get you going.
And we'll be dealing with recordsets in today's video, so if you don't know what those are, go watch this. Recordsets are very powerful things. If you've never used them before, you can loop through records in your table and do all kinds of cool stuff.
I also have another similar video titled "BOF and EOF: How to Check for Beginning of File and End of File," which is very handy for checking for empty recordsets. Today, we're talking about something similar, but there are some extra nuances too. It couldn't hurt you to go watch this one first, or go watch this one after if you want some deeper understanding.
And I'm going to talk about using the Immediate Window today, or the Immediate Pane, whichever one you want to call it, to troubleshoot your recordsets today. So go watch this if you don't know what that is. These are all free videos. They're on my YouTube channel and on my website. Go watch those and come on back.
All right, so what exactly is Error 3021? Well, this happens when your VBA code tries to read, change, delete, or otherwise use a recordset record, but there isn't a valid record currently selected.
Now, the phrase "no current record" can be a little misleading. A lot of people immediately think, "Oh, my recordset returned no records," and yes, that is one common cause, but it's not the only cause. The recordset can still contain 100 records, and you can still get Error 3021 if your record pointer has moved beyond the last record or before the first one.
So the big idea for this lesson is simple: Error 3021 describes where you are positioned in the recordset, not necessarily how many records the recordset contains.
So let's go over the terminology before we get into the code. A recordset is an object in VBA that represents records returned from a table or query. Think of it like a small stack of customer cards.
Normally, VBA is positioned on one of those cards. The selected card is the current record. If I say, "Give me RS!FirstName," VBA looks at the current card and reads its FirstName field.
But we can move that position around with commands like MoveNext and MovePrevious, and there are positions just outside the available records. There's a position before the first record called BOF, and one after the last record called EOF.
In fact, I talk about this more in that other video that I mentioned, that BOF/EOF video. I used blocks as the analogy in that record. You get blocks representing your individual records, and before them, you've got a BOF, and after them, you've got an EOF, beginning of file and end of file.
Now, those are real positions as far as the recordset is concerned, but they aren't actual records. Trying to get a field value there is asking for the phone number on the customer card just past the end of the filing cabinet.
If you're old enough to remember Rolodexes and all that, you've got a divider for A and then a divider for B. Those are actual records. Those don't have phone numbers and first names and last names on them. It's like you're trying to read the phone number off one of those divider cards.
Except your recordset has two dividers. There's just one at the beginning and one at the end. And there's just two of them. You can think of them like that: blocks, index cards, whatever works for you. I've got lots of analogies, folks.
But if there's no card, there's no record.
Now, here's the easiest way to produce Error 3021. We open a recordset based on CustomerT and ask for a CustomerID that doesn't exist, 99999. And it's not German, it's 9. No, it's just, I'm half German. I can make that joke.
Assuming the customer doesn't exist, this query returns zero records. Notice that opening the recordset itself doesn't necessarily fail. We still have an RS object. It's just an empty recordset.
Then the next line says, "Give me the FirstName." Okay, FirstName from which customer? There isn't one. We never got a current record because the query found nothing. So VBA raises Error 3021. The record wasn't found.
Now, this is an important distinction in Access programming. An empty result is often a perfectly normal business outcome. Maybe the user searched for a customer who isn't in the database. So the problem isn't that zero records came back. The problem is assuming a record came back and immediately trying to use one.
So the normal defensive check right after opening a recordset is this. You say:
If RS.BOF And RS.EOF Then
Do your thing.
BOF means beginning of file, which in practical terms means we are before the first record. EOF means end of file, meaning we are after the last record.
Now, for an empty recordset, immediately after opening it, both properties are true. Again, I talk about this more and why that is in the other video. There are no records between the BOF and the EOF markers, so there's nothing in the middle. It's like a book with no pages. That's not much of a beach read, or it's going to be a boring airplane ride.
So if both are true, display a useful message, exit the procedure, or take whatever other action makes sense for your application. Only after that check do we know that we have a record and can read RS!FirstName.
Now, here's the part that catches even experienced developers once in a while. Imagine that your recordset contains 100 customers. You move to the last customer with MoveLast. At that point, everything is fine. You're on a valid record.
Then you run MoveNext again. Those 100 customer records are still there, but now your position is one step beyond the final customer. EOF becomes true. There is no current record.
So if the next line tries to display RS!FirstName, VBA has nowhere to get it from, and you get Error 3021.
This is why the error message doesn't necessarily mean your query failed or came back empty. It can simply mean your code walked off the end of the available records. It's like when they don't finish the bridge and you're walking onto the bridge and it... no, not another analogy.
It's like you've got a filing cabinet with 100 files in it, and you're standing there asking for file number 101. There isn't one there.
All right, so an empty recordset right after you open it normally has BOF = True and EOF = True. There are zero records.
Now, a recordset that has records and you walked off the end using MoveNext, now you're going to have BOF = False and EOF = True.
And the reverse can happen too if you walk backwards too far with MovePrevious. BOF will be True and EOF will be False. The records exist, but your pointer has just passed them.
In all three cases, there's no current record, so field references to both don't work. That's why you get the error message.
Now, another common situation involves FindFirst. Maybe you have a recordset with lots of customers, and you ask VBA to find a specific CustomerID. If that customer isn't there, you can't just assume that FindFirst succeeded and go straight to RS!FirstName.
So after FindFirst, check the NoMatch property. If NoMatch is True, that means the search didn't locate the record that you asked for. Then you can tell the user that the customer wasn't found, exit the procedure, or handle it however you want to.
Now, I'm not going to turn this into a whole FindFirst, FindNext lesson today because that would take a lot of time. But I do cover a lot of that in my Fitness Database Series, specifically Part 18.
This is a great series. A lot of people might have skipped it because it says fitness database, and it's not just about fitness. It's about building a database. Fitness just happens to be the background.
So if you want to learn all kinds of cool tips and tricks, go watch my Fitness Database Series.
But the habit I want you to take away is whenever your code searches for a record or you're navigating through records, verify that you found one before trying to use it. Queries can return nothing. Searches can miss. That's normal, and good VBA will expect that.
Now, how do you debug for it? Well, when 3021 pops up, don't just close the message and hope it goes away, and don't just stick an On Error Resume Next in front of everything. It has its place, but this is not it.
In this case, click Debug. Access will highlight the line that failed. Very often, it'll be a line regarding a read of a field, assigning a field, editing a record, or deleting one.
Then use the Immediate Window right in the VBA editor. Again, I've got a separate video on that. You can ask it questions there. You can type in stuff right as your code is paused right there.
Type in:
? RS.BOF
And it'll tell you: are you at the BOF? Are you at the EOF? If that's the case, you can't message box what you're trying to message box because there's no record there.
If both are True, that's going to tell you your recordset is empty. If one is False and the other is True, you've got records, but something's going on. Then you can figure out how your pointer got there, step through the execution, and see what's going on.
All right, so to wrap it up, Runtime Error 3021 means your code expected a current record, but there wasn't one at the position where VBA was looking.
Sometimes the recordset is empty immediately after opening it. An empty recordset has BOF and EOF both set to True.
But don't make the mistake of assuming 3021 means that there were no records. You might have just moved past the last record or before the first record.
After FindFirst, check NoMatch. In loops, make sure you test your boundaries before using fields.
And again, don't just slap On Error Resume Next on as the fix. That's not fixing the flat tire. That's turning up the radio so you don't hear the boom, boom, boom, boom, boom, boom, boom.
Check the recordset state before you use it, and Error 3021 becomes a straightforward problem to diagnose and prevent.
If you want to learn more, check this video out about error handling. I'll show you some better ways to make sure your code doesn't pop up all kinds of weird errors.
Here's another good video to find out whether or not records exist. And if you want to learn more about recordsets and proper recordset navigation, I start covering them in Access Developer 16.
Very good class. We've got recordset navigation in Developer 18, more with recordset loops in Developer 24.
I spend lots and lots of time on recordsets in the Developer Series because they're really powerful. They're almost as good as SQL because you can work iteratively with them.
I think of SQL as working with records in batches, but recordset loops can handle records one at a time in a large volume. They might be a little slower than SQL, but you can do different things with each record depending on what it needs.
So there's a lot to recordsets. But there you go. Now you know what Error 3021 is, what to look for, and how to fix it.
And that is going to be your TechHelp video for today. I hope you learned something.
Live long and prosper, my friends. I'll see you next time. Intro In this lesson, we will explain Microsoft Access Runtime Error 3021, "No Current Record," and why it can occur even when a recordset contains records. We will discuss BOF, EOF, recordset navigation, empty recordsets, FindFirst and NoMatch checks, and using the Immediate Window to identify where your code lost its current record. Quiz Q1. What does Microsoft Access Runtime Error 3021, "No Current Record," mean?
A. VBA tried to use a recordset when there was no valid current record selected
B. The database file is corrupted
C. The recordset object was not declared
D. The query contains invalid SQL syntax
Q2. Does Error 3021 always mean that a query returned zero records?
A. Yes, it only occurs with empty query results
B. No, it can also occur when code moves before the first record or after the last record
C. Yes, unless the recordset is editable
D. No, but only if the recordset contains exactly one record
Q3. What happens if code opens an empty recordset and immediately tries to read RS!FirstName?
A. Access automatically creates a blank record
B. The field returns Null
C. Runtime Error 3021 occurs
D. Access moves to the first record automatically
Q4. Immediately after opening an empty recordset, what are the values of BOF and EOF?
A. BOF = False and EOF = False
B. BOF = True and EOF = False
C. BOF = False and EOF = True
D. BOF = True and EOF = True
Q5. What is the recommended way to check whether a recordset is empty immediately after opening it?
A. If RS.BOF And RS.EOF Then
B. If RS.RecordCount = 0 Then
C. If RS.NoMatch Then
D. If RS.CurrentRecord = 0 Then
Q6. A recordset has records, but code calls MoveNext after the last record. What is the resulting state?
A. BOF = True and EOF = True
B. BOF = False and EOF = True
C. BOF = True and EOF = False
D. BOF = False and EOF = False
Q7. A recordset has records, but code calls MovePrevious before the first record. What is the resulting state?
A. BOF = True and EOF = False
B. BOF = False and EOF = True
C. BOF = True and EOF = True
D. BOF = False and EOF = False
Q8. After using FindFirst to locate a record, what should code check before reading a field from the recordset?
A. The RecordCount property
B. The BOF property only
C. The NoMatch property
D. The Filter property
Q9. What does RS.NoMatch = True mean after a FindFirst operation?
A. The recordset contains no records at all
B. The requested record was not found
C. The recordset is positioned on the first record
D. The recordset cannot be edited
Q10. When Error 3021 occurs during debugging, what is a useful first step?
A. Add On Error Resume Next to the procedure
B. Close Access and reopen the database
C. Click Debug and inspect the line that failed
D. Delete and recreate the query
Q11. Which Immediate Window checks can help determine why Error 3021 occurred?
A. ? RS.BOF and ? RS.EOF
B. ? CurrentDb.Name and ? Application.Version
C. ? Err.Description and ? Screen.ActiveForm
D. ? DoCmd.OpenForm and ? MsgBox
Q12. Why is On Error Resume Next generally not a good fix for Error 3021?
A. It permanently deletes the recordset
B. It hides the error instead of checking and correcting the recordset state
C. It prevents FindFirst from working
D. It changes all recordsets to read-only
Q13. In a recordset loop, when should code test BOF or EOF boundaries?
A. After reading fields from the current record
B. Before using fields from the current record
C. Only after closing the recordset
D. Only when the recordset has more than 100 records
Answers: 1-A; 2-B; 3-C; 4-D; 5-A; 6-B; 7-A; 8-C; 9-B; 10-C; 11-A; 12-B; 13-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 covers Microsoft Access Runtime Error 3021, "No Current Record." This error can be confusing because it does not always mean that your recordset returned no records. In many cases, the recordset may contain plenty of records, but your code has moved to a position where there is no valid current record available.
This is a developer-level topic, so I recommend having a basic understanding of VBA and recordsets before working with this material. If you are new to VBA, my Intro to VBA class provides a good foundation. You should also understand what recordsets are and how they are used to work with groups of records returned by tables or queries.
Error 3021 occurs when VBA tries to read, edit, delete, or otherwise use a record in a recordset when there is no current record selected. For example, this can happen when you attempt to retrieve the value of a field such as FirstName, but the record pointer is not currently positioned on an actual record.
The important point is that "No Current Record" describes the current position within the recordset. It does not necessarily describe the number of records in the recordset.
Think of a recordset as a collection of customer cards. When VBA is positioned on one of those cards, that card is the current record. You can retrieve values from its fields, such as a customer's name, phone number, or ID number.
However, recordsets also have positions before the first record and after the last record. These positions are called BOF and EOF.
BOF means Beginning Of File. In practical terms, it means the record pointer is positioned before the first available record.
EOF means End Of File. It means the record pointer is positioned after the last available record.
Neither BOF nor EOF represents an actual record. They are simply boundary positions around the records. If your code tries to read a field while the recordset is positioned at BOF or EOF, Access raises Error 3021 because there is no actual record from which to retrieve the field value.
One of the most common causes of Error 3021 is opening a recordset with a query that returns zero records. For example, suppose you open a recordset to locate a customer by CustomerID, but the requested customer does not exist. Access can still create the recordset object successfully. The recordset itself exists, but it contains no records.
The problem occurs if your code immediately assumes that a customer was found and attempts to read a field from the recordset. Since there is no current record, Access raises Error 3021.
A query returning no records is not necessarily an error. It may be a perfectly normal outcome. Perhaps a user searched for a customer who has not yet been entered into the database. The real programming error is assuming that a record was returned without first checking.
After opening a recordset, you should verify whether it is empty before attempting to use any fields. An empty recordset will normally have both BOF and EOF set to True immediately after it is opened. This indicates that there are no records between the beginning and end boundaries.
If both BOF and EOF are True, you can display a message, exit the procedure, or take whatever action makes sense for your application. Only after confirming that the recordset contains at least one record should you attempt to read or modify its fields.
Error 3021 can also happen when the recordset does contain records, but your code moves past the available records.
For example, imagine that a recordset contains 100 customer records. If you move to the last record, you are still on a valid current record. However, if you then execute MoveNext one more time, the record pointer moves beyond the final record. EOF becomes True.
The recordset still has 100 records in it, but there is no longer a current record. If your next line of code tries to read a field value, Access raises Error 3021.
The opposite can happen when moving backward through a recordset. If you use MovePrevious while positioned on the first record, the pointer moves before the first record. BOF becomes True. The records still exist, but the pointer is no longer positioned on one of them.
The state of the BOF and EOF properties can help you determine exactly what happened.
If BOF is True and EOF is True, the recordset is empty.
If BOF is False and EOF is True, the recordset contains records, but the pointer has moved past the last record.
If BOF is True and EOF is False, the recordset contains records, but the pointer has moved before the first record.
In all of these cases, there is no current record, so attempting to use a field from the recordset will cause Error 3021.
Another common situation involves using FindFirst to search for a specific record. You may have a recordset containing many customers and use FindFirst to locate one particular CustomerID. If the requested customer is not present, FindFirst does not position the recordset on a valid matching record.
After using FindFirst, check the NoMatch property before attempting to use any fields. If NoMatch is True, the search failed to find the requested record. At that point, you can notify the user, exit the procedure, or perform another appropriate action.
The general rule is simple: whenever your code searches for a record, moves through a recordset, or expects a query to return a result, verify that a valid record is available before attempting to use its fields.
When you receive Error 3021 during development, do not simply dismiss the error message or add On Error Resume Next to hide it. On Error Resume Next may have appropriate uses in certain situations, but it is not a proper solution for an invalid recordset position.
Instead, use the Debug option when the error occurs. Access will highlight the line of code that failed. In most cases, the highlighted line will be attempting to read a field, assign a field value, edit a record, or delete a record.
At that point, use the Immediate Window in the VBA editor to examine the recordset's state. Check the BOF and EOF properties to determine whether the recordset is empty or whether your code moved beyond one of its boundaries.
If both BOF and EOF are True, your query returned no records.
If only EOF is True, your code likely moved past the final record.
If only BOF is True, your code likely moved before the first record.
This information helps you determine where the problem began. You can then step through your code and identify the MoveNext, MovePrevious, FindFirst, query, or other operation that left the recordset without a valid current record.
To summarize, Runtime Error 3021 means that VBA expected a current record but could not find one at the recordset's current position.
Sometimes this happens because the recordset is empty. In that case, both BOF and EOF are True.
Other times, the recordset has records, but your code has moved past the last record or before the first record.
After FindFirst, check NoMatch. When navigating through recordsets, always test the recordset boundaries before using fields. When opening a recordset that may return no records, verify that it contains at least one record before assuming that a current record exists.
Recordsets are extremely useful in Access VBA because they allow you to process records one at a time. SQL is often ideal when you want to work with groups of records in batches, while recordset loops are useful when each record may require separate logic or processing. Understanding proper recordset navigation is essential if you want to use recordsets reliably in your Access applications.
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 Understanding Access Runtime Error 3021
What "No Current Record" really means
Recordset current record positioning
BOF and EOF recordset positions
Empty recordsets with BOF and EOF
Errors after moving past the last record
Errors after moving before the first record
Checking recordsets before reading fields
Using NoMatch after FindFirst
Debugging recordset position with Immediate Window Article Microsoft Access Runtime Error 3021, "No Current Record," occurs when VBA tries to read, edit, delete, or otherwise use a record in a recordset when no valid record is currently selected.
The message can be misleading because it does not necessarily mean that the recordset contains no records. A recordset may contain many records, but your code can still receive this error if it has moved beyond the last record or before the first record.
A recordset has a current position, sometimes called the record pointer. When the pointer is positioned on an actual record, you can read field values, modify the record, or perform other actions. If the pointer is outside the available records, there is no current record to work with.
The two important properties for checking recordset position are BOF and EOF. BOF means Beginning Of File, and EOF means End Of File. These names come from older file-processing terminology, but they are still used in Access recordsets.
An empty recordset has both BOF and EOF set to True immediately after it is opened. This means there are no records between the beginning and end positions. In that situation, attempting to read a field value causes Error 3021 because there is no record available.
For example, suppose your code opens a recordset to find a customer using a customer ID. If no customer matches that ID, Access can still create the recordset object successfully. The recordset exists, but it contains zero records. The error occurs only when your code assumes a matching customer was found and immediately tries to read a field such as FirstName, Email, or Phone.
The correct approach is to check whether the recordset is empty immediately after opening it. If both BOF and EOF are True, handle the situation appropriately. You might tell the user that no matching customer was found, return a blank value, create a new record, or exit the procedure. The important point is that your code should not attempt to use record fields until it has confirmed that a valid record exists.
Error 3021 can also occur when the recordset does contain records, but your code has navigated past them.
For example, imagine a recordset containing one hundred customers. If your code moves to the last record, it is still positioned on a valid customer. However, if it then moves forward one more time, the pointer moves past the final record. EOF becomes True, and there is no current record. The recordset still contains one hundred customers, but your code is no longer positioned on any of them.
The same issue can happen in reverse. If your code moves backward before the first record, BOF becomes True. Again, the recordset may contain records, but there is no current record because the pointer is before the available data.
In practical terms, these are the common recordset states that can cause Error 3021. An empty recordset has BOF set to True and EOF set to True. A recordset that has moved past the final record has EOF set to True. A recordset that has moved before the first record has BOF set to True. In every case, trying to use a field value will fail because there is no valid current record.
Searching for records can create the same problem. If you use a recordset search operation to find a specific record, do not assume the search succeeded. After searching, check the recordset's NoMatch property. If NoMatch is True, the search did not find the requested record. Your code should handle that result before attempting to read or modify fields.
This is especially important in recordset loops. A typical loop should verify that it has not reached EOF before using the fields in the current record. After processing the record, it can move to the next one and repeat the check. This prevents the code from trying to process a record after it has moved beyond the end of the recordset.
When Error 3021 occurs, click Debug in the error dialog rather than dismissing the error. Access will highlight the line that failed. The problem is often a field reference, an attempt to edit a record, or an attempt to delete a record.
At that point, use the Immediate Window in the VBA editor to inspect the state of the recordset. Check whether BOF is True, whether EOF is True, and, if you performed a search, whether NoMatch is True. These values reveal whether the recordset is empty, whether the code moved too far forward or backward, or whether a search failed to find a match.
Do not treat On Error Resume Next as the solution to this problem. Suppressing the error does not fix the recordset position. It only hides the fact that your code tried to use a record that was not available. The proper fix is to check the recordset state before reading, updating, or deleting records.
Runtime Error 3021 is usually straightforward once you understand what it means. It does not always mean that your query returned no records. It means that, at the moment your code tried to use the recordset, there was no valid current record selected. Check for empty recordsets, confirm successful searches, and test BOF and EOF during navigation. Doing so will prevent the error and make your Access VBA code more reliable. Primary Topics
Runtime Error 3021, no current record, DAO recordset navigation, BOF and EOF states, empty recordsets, MoveNext and MovePrevious boundaries, FindFirst and NoMatch, Immediate Window debugging
Secondary Topics
defensive VBA coding, field references, recordset current position, avoiding On Error Resume Next
|
