Open Last Customer
By Richard Rost
15 months ago
Add Button to Access Form for Previous Record In this Microsoft Access tutorial, I will show you how to add a button to a form to easily reopen the previous record. Ideal for switching between records like customers or orders, the tutorial covers using VBA, temp vars, and events with minimal code. MembersIn the extended cut, I will show you how to create a list of previously opened customers in order, making it easy to double-click and open any customer from this list. This feature allows for quick navigation between records with a simple requery to refresh the list. 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, Add button Access form reopen previous record, VBA programming Access forms, Access form navigation, TempVars Access, form open specific record, Access form events, on current event Access, Access form caption property, record switching Access, Access form load event, Access button visibility toggle
Intro In this video, I will show you how to add a button to your Microsoft Access form that lets you quickly reopen the last record you viewed, making it easy to switch back and forth between two records such as customers, products, or orders. You'll learn how to use VBA and TempVars to track which record was previously open, initialize variables, handle the On Load and On Current events, update the form's caption for debugging, and use DoCmd.OpenForm to jump to specific records. I'll also show you how to conditionally hide the button when there is no previous record to return to.Transcript In today's video, I'm going to show you how to add a button to your Microsoft Access form to reopen the previous record. I know the title says Open Last Customer. I went through a couple of different titles, but the last customer you were on. This is helpful if you need to switch between two records, for example. It could be customers, products, orders, or whatever you want. You can put this button in any form and switch between two records.
I have to do this a lot myself because on my website I get a lot of people who sign up with two different email addresses, the same person, or they'll mistype one of them and set up two accounts by accident. I'll have to open up, let's say, this guy William Riker; I'll put that over there, and then I open up Jordy LaForge. I want to switch back and forth between them because I've got to maybe copy some information. That's what this "go to last" button does: go to William and back to Jordy and then back to William and then back to Jordy. You just click this and it'll go to the last person you open. So if I'm on Jordy and I open up Tom, now I can click and go back to Jordy. I'll just go back to whoever the previous record was. I find this helpful for myself and I'm going to show you how to do it. It's not that hard.
This is going to be a developer-level video. What does that mean? Well, that means I have a bunch of prerequisites for you to watch first. If you've never done any VBA programming before and you want to learn, go watch this video. It teaches you everything you need to know in about 20 minutes. You'll need to know how to use an if-then statement. We're going to use Adam's favorite temp vars, so make sure you know how those work. You should know what null values are and how to check a form with is null. We're going to open a form to a specific record. We're going to use the on current event when we move from record to record. We're going to modify the form's caption property. You should know how string concatenation works. If you don't know any of these things, go watch all these videos first. These are all free. They're on my YouTube channel. They're on my website. Go watch them and come on back.
None of this is really very hard. In fact, we don't need that much code. I think the whole thing is less than 20 lines of code total. But ideally, what we're going to do is open a form. Then we're going to store it in a memory variable. I'm going to use temp vars. Temp vars are better than regular variables because if your database does throw an error, temp vars will retain their value. I talk about this in the temp vars video, and they're easy to use. Then we move to another record or open a different one from maybe the customer list. Like I showed before, we'll just set that one as the last record that was open. This will not be the current record that's open. If you hit the back button, let's just go to the last one. It's all accomplished with just two variables.
Let's go into the design view of the customer form. Let's open up the form's properties, and we'll go to the form load event, the on load event... that will bring up my code builder. Whoops, it's really huge. We'll just shrink that down. We don't need the project explorer, so we can close that pane. In the form load event, this is where we're going to initialize our temp vars. Normally, if you don't initialize them, they'll start out as null. I don't want to have to keep checking for nulls everywhere, so I'm just going to initialize them right here. I'm going to say if is null, what are we going to call it? Let's call it temp vars. We're going to make two of them last customer ID. If that's null, then we're going to set it equal to zero. I'm just going to copy this and keep typing it. We're going to set it equal to zero. So if it's null, make it zero. It's that simple. I don't want to have to keep checking everywhere for is null. I know at this point when the form loads that both of these are set to zero. This one's going to be this customer ID. We got this customer ID and last customer ID. We're going to put up here in a comment... initialize variables.
You should always initialize your variables. Don't assume what they start at. You make them start at something. In some languages like C or C++, you have to initialize them. Otherwise, it could be any value at all. VBA is pretty good about initializing stuff to null or zero for integers and stuff. But you want to make sure you initialize your variable.
That handles when the form opens. Now when the user moves from record to record, this is where we do the checking to see if they opened a different record than they were on previously. We're going to do that in the form current events. Come up here and find form current. Remember from our current video that the form current event runs when you move from record to record or when the form opens because that's basically moving to the first record, which is moving to a new record.
At this point, I want to compare the current customer ID with whatever is in the this customer ID temp var. We're going to call it a variable from that one. If they're different, that means I've moved to a new record. If they're the same, that means they're not. You could open up the same customer like four times in a row. You don't want to change the variable at that point. We're going to come in here and say if I don't want to have to keep typing it. I'll just copy that to the clipboard and paste it every time. So if my this customer ID, if it's different from the customer ID on the form, then they opened a different customer. We'll do some stuff and end if.
Now what do we do? Well, I'm going to take whatever value is in this customer ID and set it as last customer ID so I know where they were last. So again, let's copy this. We're going to say last customer ID equals this customer ID. Now, if you remember from the temp vars video, whenever you're setting a temp var like that, if it's not a constant value, you have to put .value in here. It's just one of the quirks of temp vars. It's a small price to pay for being able to use them. So take the current this customer ID and put it in the last customer ID so we know the last previous one we were on. Now we can set this customer ID equals the customer ID on the form. And again, .value because we want the actual value of the ID. It's basically like we got two little boxes. We're going to take the customer ID and put it in the previous box. Then we go to another customer ID and take it and then move the other one back to the box and you're just swapping boxes basically.
What I like to do is a little developer tool for myself: I like to set the form caption so you can see what's going on with these things. We're going to say me.caption. This is just something you'll do while you're developing so you can watch what the values are. We're going to say me.caption. That's the form's caption. We're going to say this is [copy the this customer ID]. And last, let's put a couple of spaces in there. Last is the last customer. Just so we can see what they are when this thing runs.
Save that. Let's debug and compile once in a while. Come back out here. I'm going to close this and let's open it up. When it opens, they were both initialized to zero when the form opened. That's the form open event. Then the on current event ran when the first record loaded and it set this to one and last is still zero. Now if I go to the next record, look what happens. This is now two; last got set to one, and so on. See, three and two. Four and three. If I close this and open up somebody else like Julian here number 14, look at that: the temp vars are still active. This is now 14, and last was four. If I go to nine, this is nine, and last was 14. See, we're just moving that variable back. We're swapping it. We're just moving it from box to box. You could do ten of these if you really wanted to. In fact, in the extended cut, I'm going to show you how to do a whole bunch of as many as you want in all this box.
Now all we need is a button over here that will open up customer ID 14 because it was the last one. This is the easy part. We can do this easily. Come in here. I'm going to make a button. I'll just copy this one: copy, paste. Go to last. We're going to give it a name so Alex doesn't yell at us. You don't know who Alex is. Well, sometime on my website, this will be the go to last button. Then we will right-click and go to its build event. In here, we're simply going to say do command.open form customer F comma comma, chameleon. I say that because then you remember there are three commas there. The where condition, some of the stupid things I do, I do them so that they stick with you when you remember. Oh, yeah. Okay. Customer ID equals [temp vars last customer ID]. Now we can do some checks in here, too. I want to make sure it's not zero. Let me copy this. You could say if temp vars are equal to zero, that means there isn't one. Actually, we're going to say not zero. Then do that. Otherwise, just don't do anything. Because there isn't a last customer to go back to.
Debug, compile. Now our button should be functional. If I open up Jim Kirk, you see the last one was nine. So go to the last one. Whoop. Parkly. Then I can go back to Kirk. See, it's just swapping them back and forth. Open up Miles O'Brien, go to last. See, go to number 10, go to the last one. And that's it. That's all you really got to do. Oh, wait. Hold on. Someone's beaming in. Hold on. That's more for Ferengi traders.
One more thing I'm going to show you is maybe let's hide this button if there isn't a last one to go back to, because when you first open up the database, these will both be initialized. So what we can do is we can say in our code in here. We can say right here: show or hide the button. So if the last customer ID is still zero, then [go to last button].visible equals false. Otherwise [go to last button].visible equals true. That'll only happen once when you first open the database. And you first open that form the first time. But it's nice to not have the button if you can't do anything. In which case, then you probably won't even need this. But that's okay. Let's debug, compile. I'm going to close it. Oh, I did hit the wrong button there.
Oh, by the way, remember my previous video. It's good to close all these things down to keep your database running faster. So I'm going to hit control F4 and hold it down. Close all those windows. We're going to close this. We're going to shut the database down. We're going to open it back up again. Now the first time I run it, the button isn't there. Why? Because the last is zero. There isn't one. But if I close this now and go to somebody else, well, I messed myself up again by accident. If I open up somebody else, now you can see the button is there. And I got values. You can hide this if you want to. Pretty cool. I use this constantly in my database because I get this a lot. At least once a week, I get someone who's like, I accidentally set up two records, two different accounts with different email addresses. Can you move on? Okay. Yeah, I'm going to eventually code it so I can just, with one button, move all their stuff over. But it's not annoying enough yet. I tend to code things for myself when it gets to the point where I'm spending so much time on it that it gets annoying.
In the extended cut, I'm going to show you how to make a list right here with all of the previously opened customers in order. So this is the old footer bag. This is the last one I was on: Mr. Data, Miles O'Brien, and so on. I just double-clicked on Jodie LaForge. It brought him up and then as you're going to these, you can hit requery. It'll refresh the list. Close this. Let's say I open up the customer list and I open up Tashi R and then I go to Christopher Pike. If I close this stuff down now and requery this list, oh, look who's up top: Tashi R, Christopher Pike. You can easily go to any of the previously open customers by just simply double-clicking on it. The easy that is. That's pretty cool. We'll cover that in the extended cut.
As a reminder, Silver members and up get access to all of my extended cut videos and a whole bunch of other cool stuff. So click on that blue join button to join today. If you like learning with me, come to my website and check out my developer lessons. I got lots of them. I think I'm up to 48 right now. There are hundreds of hours of lessons on my website. Check it out. There's a link down below.
But that, my friends, is going to be your TechHelp video for today. I hope you learned something. Live long and prosper. I'll see you next time.
TOPICS:
Adding buttons to a Microsoft Access form
Switching between records in Access
Using VBA to navigate records
Opening a form to a specific record
Using the On Current event in Access
Initializing variables in VBA
Checking for null values with IsNull
Using TempVars in Access VBA
Storing values in memory variables
Setting form captions for debugging
Swapping values between variables
Creating navigation buttons with VBA
Conditionally hiding buttons based on values
Opening records using DoCmd.OpenForm
Debugging and compiling VBA code
Creating a list of previously opened records
COMMERCIAL:
In today's video, we're discussing how to add a button to your Microsoft Access form to toggle back to the last record you viewed, like switching quickly between customers or products. We'll cover initializing temp vars to track the current and last records with minimal coding. You'll learn when and how to use the form load and on current events, adjust the form's caption property, and even enhance user experience by showing or hiding the button based on availability. Plus, a sneak peek into our extended cut will reveal how to manage a list of previously opened records. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the primary purpose of the button discussed in the video?
A. To delete the current record
B. To navigate to the next record
C. To reopen the previous record
D. To save changes to the current record
Q2. Which programming language is used to implement the solution in the video?
A. Python
B. C++
C. VBA (Visual Basic for Applications)
D. Java
Q3. Why are temp vars preferred over regular variables according to the video?
A. They have a larger storage capacity
B. They retain their values even if the database throws an error
C. They are more secure
D. They are easier to understand
Q4. What event is used to initialize the temp vars when the form opens?
A. Form Close event
B. Form Load event
C. On Click event
D. Form Unload event
Q5. When is the "on current" event triggered in an Access form?
A. When a new customer is added
B. When the form is closed
C. When moving from one record to another or when the form opens
D. When a button is clicked
Q6. What is the purpose of comparing the current customer ID with the "this customer ID" temp var?
A. To create a backup of the database
B. To check if the form needs updating
C. To determine if the user has moved to a new record
D. To delete the previous record
Q7. How many temp vars are utilized in the video to keep track of the records?
A. One
B. Two
C. Three
D. Four
Q8. What does the video suggest adding to the form's caption for development purposes?
A. Database open status
B. Current and last customer IDs
C. User login time
D. Total number of records
Q9. What condition is checked before executing the command to open the "last" customer?
A. If the database is online
B. If there are more than ten customers
C. If the last customer ID is not zero
D. If the customer is verified
Q10. What additional feature is demonstrated in the extended cut of the video?
A. Sending an email notification
B. Hiding the main access menu
C. Displaying a list of previously opened customers
D. Exporting the database to Excel
Answers: 1-C; 2-C; 3-B; 4-B; 5-C; 6-C; 7-B; 8-B; 9-C; 10-C
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 TechHelp tutorial from Access Learning Zone will guide you on how to add a button to your Microsoft Access form that allows you to reopen the previous record you were viewing. This can be quite handy when you need to frequently switch between two records, whether they are customers, products, orders, or any other data type. You can apply this technique to any form to facilitate easy navigation between records.
I often find myself in need of this feature on my website when users mistakenly create multiple accounts with different or incorrect email addresses. For example, I might need to access William Riker's record, then switch to Jordy LaForge's record, and go back and forth between them to transfer some information. This "go to last" button is designed to handle such tasks by simply going back to the previous record you accessed. So if you're currently viewing Jordy and you navigate to Tom, you can easily go back to Jordy at the click of a button. It's a very convenient tool, and I'll demonstrate how you can implement it. It's not too difficult to set up.
This tutorial is aimed at a developer-level audience, which means there are some prerequisites you should be familiar with first. If you're new to VBA programming, I recommend watching an introductory video that explains the basics in about 20 minutes. You should understand how to use if-then statements, and be familiar with temporary variables (temp vars), as they will be used here. Knowing about null values and how to check for them using is null on a form is important. You should also know how to open a form to a specific record and use the on current event, as we'll be using this when moving between records. Moreover, you should have knowledge of modifying the form's caption property and how string concatenation works. If you don't have these skills yet, I have videos on my YouTube channel and website that cover these topics. They're all free of charge.
This entire task will require less than 20 lines of code. We'll start by opening a form and storing information in a memory variable using temp vars. Temp vars have the advantage of retaining their value even if your database encounters an error. I explain this further in a separate video on temp vars. After moving to or opening a different record, we'll update the records accordingly. The idea is that by using just two variables, we can efficiently switch between records.
In the customer form's design view, we'll access the form's properties and handle the form load event to initialize our temp vars. By initializing these variables right away, we avoid the need to constantly check for nulls. If they are null, we'll set them to zero. This approach ensures that both the current customer ID and the last customer ID are set to zero when the form loads.
Once the form is opened, we'll use the form current event to monitor if the user moves to a different record. If the current customer ID differs from the temp var we have, we'll update the records, essentially swapping values in our two boxes. Additionally, I like to set the form's caption to display these values during development.
We'll add a button to open the last customer ID, making sure to check and handle scenarios where this ID is zero. This button will allow us to switch back to the previous record with ease.
As a final touch, I'll show you how to hide the button when there isn't a previous record to return to, which will happen when you first open the database and load a form.
This approach is helpful in managing records where you frequently switch back and forth, and can be tailored to various needs. For more detailed instructions, including how to create a list of previously opened customers, more features are covered in the extended cut available for Silver members and above.
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 Adding buttons to a Microsoft Access form
Switching between records in Access
Using VBA to navigate records
Opening a form to a specific record
Using the On Current event in Access
Initializing variables in VBA
Checking for null values with IsNull
Using TempVars in Access VBA
Storing values in memory variables
Setting form captions for debugging
Swapping values between variables
Creating navigation buttons with VBA
Conditionally hiding buttons based on values
Opening records using DoCmd.OpenForm
Debugging and compiling VBA code
Creating a list of previously opened records |
