New Record ID
By Richard Rost
36 hours ago
Safely Get AutoNumber ID After Adding a Record In this lesson, we will show you how to capture the new record ID created by Microsoft Access VBA using a recordset and AddNew. You will learn why relying on the highest ID can select another user's record in a multi-user database, how to return an AutoNumber ID from a function, and how to use that ID to find and focus on the exact record your code just added. Sophia from Porto, Portugal (a Gold Member) asks: How can I get the ID of the record my code just created without accidentally selecting another user's record? PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, Access VBA get newly added record ID, VBA AutoNumber ID after AddNew, DAO recordset AddNew AutoNumber, return ID from VBA function, Recordset FindFirst by ID, multi-user database record ID, open newly created record
Subscribe to New Record ID
Get notifications when this page is updated
More InformationTranscript Your button adds a record, but now you want to open it or put the user right back on that row. How do you know which record your code just created?
Welcome to another TechHelp video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
Today, we're going to get the ID of a newly added record in Microsoft Access VBA. We'll look at the important ID first, then I'll show you a short example from my Fitness database series. You can use the same ID for customers, orders, or whatever your database keeps track of.
So, today's question comes from Sophia in Porto, Portugal, one of my Gold members. She says, "I have a button that adds an order with VBA. After it runs, I want to open that exact order, but other people use the database too. How can I get the ID of the record my code just created without accidentally picking somebody else's order?"
Well, that's a good question, Sophia, because the record that looks newest isn't necessarily the one your code just added. And I covered this in part 31 of my Fitness database series. Let's take a look at why the ID matters, and then I'll show you an example from my class.
So, here's the answer in a nutshell. Have the routine that creates the record give you back its AutoNumber ID. Now you've got a specific key to work with instead of having to guess which row is yours.
Now, in the Access table example we're about to watch, we use a recordset and AddNew. Access assigns the AutoNumber while that new record is being created, so we can capture it there. We still have to save the record with Update, and capturing a number doesn't mean the record has been saved. But then you can return that ID as a Long to whoever called that function. And that's the basic pattern. That's how it works.
But now, let's say your button creates order 1046, and a coworker creates order 1047 a moment later. If your next step is to ask for the highest order ID, which one are you going to get? You're going to get theirs. Nothing necessarily crashes. You just end up with the wrong order, which can be worse because it looks like everything worked.
A sort order tells you where a record appears. It doesn't tell you who created it. So, you want to keep the ID that the record from your code is adding, and then you don't have to make that assumption.
All right, so here's the example. This is coming from my Fitness database series. This is where I've got a form where I pick a food item and add it to my daily food log. The record gets added, but I also want the form to find it and put the focus back on that row. It's the same problem Sophia was having with her orders.
Now, before we get to the sample code, if you haven't watched my Intro to VBA class, go watch this first. It's free. It's on my YouTube. It's on my website. Go watch it. It's about 20 minutes long. It teaches everything you need to know about VBA to get started.
And you'll need to know recordsets. So, if you've never used a recordset before, go watch this video first. It explains how to use recordsets. Go watch those, then come on back.
Now, in order to do that, we have to get the ID that was just added to the FoodLog table. And I'm doing this primarily to teach you something. So, we have to say, "You just added an item. Which item is it?" Now, we can't just rely on looking at the most recent one because IDs aren't always that way, especially if you're working with SQL Server, especially if you've got a multi-user environment, and especially if you're using random AutoNumbers or something different.
So, what we're going to do is, when we add the item to the table, we're going to say, "Hey, I need you to return that ID to me, buddy. What record you just added? Give me its ID."
Now, if we're going to do that, then we can't rely on this being a subroutine. Let's turn it into a function and return that value.
All right, so let's change this to a function. And we're going to return a Long, so As Long. This is easier than you think it is, by the way.
Right here, after we do the RSLog.AddNew, at that point, we're assigned an ID. This is different in SQL Server, by the way. In SQL Server, you don't get that ID until the record is committed. In Access, you get it immediately, so we'll have to do some modifications if we decide to put this in SQL Server. But for now, it works just fine.
At this point, I can now say, AddFoodItemToLog = RSLog. What is it? FoodLogID. Now I've got the AutoNumber that was just created, and that'll get returned as a function.
So now we'll come down here. And we'll Dim NewID As Long. And we'll say, NewID = this is not a function, so do it like that.
Now we can find that item by saying, Me.Recordset.FindFirst FoodLogID = the NewID we just added. Now we can set focus and do all that other stuff. FoodDescription up in the top there, .SetFocus. And then, how about FoodCombo = no blank, the combo box? Or, if you want to have the focus down here, that's up to you. If you're more of a keyboard person or a texting person or whatever, I don't know.
All right, save it. I'm giving a Debug Compile. Come out of me. Let's close her, open it.
Now let's do this. Let's do farewells, and then add it. And look at that. I'm sitting right on it. And the box is blank.
And so there we go. That function tells us which record it created, and the calling code then uses that exact ID to find the row. We don't have to depend on the highest number or whatever happens to be at the bottom of the form.
You can use that key for the next step in your own workflow, whether that's opening the new order or creating records related to it. Just remember that record has to save successfully before you continue working on it.
And this is just one example from my Fitness database series. This is a free series I put on YouTube a while back. I'm still working on it. There's going to be more lessons coming out, but Fitness is just the backdrop. It doesn't matter if you're into fitness. You don't care about counting calories and exercise workouts and all that. It doesn't matter. We're building a cool database, and I've put lots of tips and tricks into this series. So, check it out.
And if you want to learn more Access, here are a couple of other videos I recommend. My Creative Function videos are really good for getting a value returned back from a function, which is what we covered today. Access Developer 16 is a real deep dive into recordsets. We do lots with recordsets in this lesson. One of my favorites.
And hey, don't forget, if you enjoyed this video, if you got something out of it, hit that Like and Subscribe. It helps me out, helps the channel out, and helps more people find me, so I appreciate it. Thank you.
All right, so there you go. Now you know how to keep the ID of the record that your VBA just created, and you can use it to find the right row or open the form to the right record or do whatever you want to do with it. If they're your Legos, you put them together however you want.
And that's 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 show you how to capture the new record ID created by Microsoft Access VBA using a recordset and AddNew. You will learn why relying on the highest ID can select another user's record in a multi-user database, how to return an AutoNumber ID from a function, and how to use that ID to find and focus on the exact record your code just added. Quiz Q1. Why is using the highest OrderID after adding a record unsafe in a multi-user database?
A. Another user may add a record first, making their ID the highest
B. AutoNumber fields cannot be sorted
C. Access always assigns duplicate IDs
D. FindFirst only works with the lowest ID
Q2. What is the best way for VBA code to identify the exact record it just created?
A. Sort the table by date descending
B. Return the new record's AutoNumber ID from the routine that creates it
C. Use DMax to find the largest ID
D. Search for the last record in the form
Q3. Why should a record-creating procedure be changed from a Sub to a Function in this situation?
A. A Function can return the new record's ID to the calling code
B. A Function automatically saves all open forms
C. A Function prevents other users from adding records
D. A Function eliminates the need for a primary key
Q4. What data type was used for the returned new record ID?
A. String
B. Date
C. Long
D. Boolean
Q5. In the recordset example, when can the new AutoNumber ID be captured in an Access table?
A. Immediately after RSLog.AddNew
B. Only after closing the database
C. Before calling AddNew
D. Only after running a totals query
Q6. What still needs to happen after adding values to a new record in a recordset?
A. The record must be saved with Update
B. The table must be compacted
C. The form must be requeried before saving
D. The AutoNumber field must be manually entered
Q7. Which approach correctly moves a form to the newly added record after its ID has been returned?
A. Me.Recordset.FindFirst "FoodLogID = " & NewID
B. Me.Recordset.MoveLast
C. DoCmd.GoToRecord , , acLast
D. DoCmd.OpenTable "FoodLog"
Q8. What is the main problem with relying on a sort order to identify a newly created record?
A. Sort order determines display position, not which user created the record
B. Sort order permanently changes AutoNumber values
C. Sort order disables recordsets
D. Sort order prevents records from being saved
Q9. Why is returning the newly created ID useful after creating a record?
A. It can be used to open the exact record or create related records
B. It removes the need for primary keys
C. It guarantees that no other users can edit records
D. It automatically creates a backup of the table
Q10. What difference was mentioned regarding newly generated IDs in SQL Server?
A. The ID may not be available until the record is committed
B. SQL Server does not support ID fields
C. SQL Server always uses random text IDs
D. SQL Server does not allow records to be saved
Answers: 1-A; 2-B; 3-A; 4-C; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A
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 to retrieve the ID of a record that was just created with Microsoft Access VBA.
A common situation is having a button that creates a new customer, order, food log entry, or other record. Once that record has been added, I may want to open it, move the user to it on a form, or create related records for it. To do that reliably, I need to know the primary key value, usually the AutoNumber ID, for the exact record my code created.
It can be tempting to assume that the record with the highest ID is the one I just added. That is not a safe approach, especially in a multi-user database. Another user could add a record immediately after mine. If I then look for the largest ID, I might retrieve their record instead of my own.
This kind of problem may not generate an error. The database may appear to work normally while quietly displaying, editing, or linking information to the wrong record. That makes it particularly important to keep track of the actual ID created by my code.
The proper approach is to have the procedure that adds the record return the new AutoNumber ID to the code that called it. Rather than using a Sub procedure, I use a Function that returns a Long value. The Long value is appropriate because Access AutoNumber primary keys are commonly stored as Long Integers.
In the example from my Fitness database series, I have a form that lets me select a food item and add it to a daily food log. After adding the item, I want the form to locate that new food log record and place the user on it.
The record is added with a DAO recordset. After starting the AddNew process, Access assigns the AutoNumber value for the new record. At that point, I can read the new FoodLogID from the recordset and assign it as the return value of my function. The record still needs to be saved with Update. Capturing the ID does not itself save the record.
Back in the button's event procedure, I store the returned value in a Long variable, such as NewID. I can then use that value to find the exact record on the form's recordset. Once the form is positioned on that record, I can set focus where I want it and clear the food selection control so the user can enter another item.
This method does not depend on sorting, the highest ID, the last visible row, or any assumption about which record was added most recently. A sort order only determines where a record appears. It does not tell me which user or which routine created it.
The same technique works for orders, customers, invoices, payments, appointments, and any other table with an AutoNumber primary key. After creating the record, return its ID, save it in a variable, and use that ID for the next step in the process.
There is an important distinction when working with SQL Server. In an Access table, the AutoNumber can be available while the new record is being added through a recordset. With SQL Server, identity values are generally retrieved after the record is committed, so the process must be adjusted accordingly. However, the overall principle remains the same: retrieve and retain the ID generated for the record your own code created.
If you are new to VBA, I recommend learning the basics of functions and returned values before using this technique. It is also important to understand DAO recordsets, since they are used to add and work with records directly in Access tables.
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 Returning a new record's AutoNumber ID from VBA
Capturing an AutoNumber ID with Recordset.AddNew
Converting a Sub procedure into a Long function
Using the returned ID to find the new record
Why highest ID is unsafe in multi-user databases
Setting form focus on the newly added row Article When your VBA code creates a new record, the safest way to work with that record afterward is to capture its unique ID at the moment it is created. This is especially important in a multi-user database, where other people may be adding records at the same time.
A common mistake is to find the newest record by looking for the highest ID or the last record in a form. That may appear to work when you are the only person using the database, but it can fail when multiple users are active. For example, your code might create order 1046, and another user might create order 1047 immediately afterward. If your code then searches for the highest order ID, it could open the other user's order instead of yours.
The solution is to have the procedure that adds the record return the new record's AutoNumber ID. Instead of using a Sub procedure that performs an action without returning anything, use a Function that can return a value. The function should create the new record, capture its ID, save the record, and then give that ID back to the code that called it.
In Access, when you add a new record through a recordset, the AutoNumber value becomes available while the new record is being created. Your code can read that value from the new record and store it in a Long variable. The record should still be saved successfully before you continue working with it, but the ID can be captured as part of the insert process.
The calling code can then store the returned ID in its own variable. From there, you can use that exact ID to locate the record in a form, open another form directly to that record, create related child records, print a report for the new record, or perform any other follow-up task.
For example, suppose a user selects a food item and clicks a button to add it to a food log. The add-record function creates the food log entry and returns the new FoodLogID. The button's code receives that ID, searches the form's recordset for the record with that specific FoodLogID, and moves the form to that row. The form can then set focus wherever you want, such as back to the food selection control so the user can add another item.
This approach is more reliable than depending on sort order, the highest ID, the last visible row, or a timestamp. Sort order only controls where records appear. It does not prove which record was created by your current code.
The key idea is simple: create the record, capture its unique ID, save the record, and use that ID for the next step. Once you have the ID, you no longer need to guess which record belongs to the user or process that just created it. Primary Topics
Access VBA record creation, AutoNumber ID retrieval, DAO recordsets, AddNew and Update, converting a Sub to a Function, multi-user record identification, FindFirst navigation Secondary Topics
form focus, returning Long values, avoiding highest-ID queries, record save timing, SQL Server difference |
