AfterUpdate & DLookup
By Richard Rost
6 years ago
AfterUpdate Event & DLookup - Look up Values from Table to a Form
In this Microsoft Access tutorial, I will show you how to look up and auto populate fields in a form using the DLookup function and the AfterUpdate event in VBA (Visual Basic). Don't worry if you've never done any programming before. I'll take it step by step.
Chris (Gold Member) asks, "I assign zone numbers to customers based on the state the customer is in. However, this isn’t a rule. There may be exceptions, such as if a sales rep brings in a new customer from out of his zone. Is there a way to automatically fill in the default zone for the customer when I enter his state, but still be able to change it?"
Members
I'll show you how to prompt the user for the Zone number if the State isn't found in the table. We will learn how to use the InputBox, NZ, CInt, and IsNumeric functions, and learn about type casting.
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!
 Recommended Course
Pre-Requisities
Links
Intro In this video, I will show you how to use the AfterUpdate event and the DLookup function in Microsoft Access to automatically fill in a default zone number for customers based on their state when entering data on a form. We'll set up the supporting tables, discuss why an AutoNumber is useful, walk through building the lookup formula, and demonstrate how AfterUpdate can auto-populate the zone field while still allowing for manual changes when there are exceptions.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how to use the AfterUpdate event and the DLookup function to look up and auto-populate fields in a form.
Today's question comes from Chris, one of my Gold members. Chris asks, I assign zone numbers to customers based on the state the customer is in. However, this isn't a rule. There may be exceptions, such as a sales rep bringing in a new customer from out of his zone. Is there a way to automatically fill in the default zone for the customer when I enter his state, but still be able to change it?
Chris's email explains further that zones have different components to them. For example, a zone might have a particular shipping rate or a particular sales rep, so they use a zone to track all that information. Now the problem Chris is running into is that he stores the zone number in the customer table, which he should, but every time he types in a new customer he has to manually look up what the zone is for, let's say, New York, and then type it in himself. He wants to know if Access can do that for him.
You can't just do it with a relationship because if you relate New York to the zone, then you can't change it for each customer. So you need a way for Access to look that value up when you type in the state and fill it into the customer table.
Here I have my basic customer template. Here is a customer's form and you can see here I have the address. We're going to have to add a zone number to this form first, so let's add it to the table. Let's go to customerT, design view. Down here we'll put in zone and that's a number. Long integer, fine. Close that, save changes, sure.
Now we'll need another table to look up that zone number. So let's create table design. Now I can't use zone number as an AutoNumber because each zone might be assigned to multiple states, but I do like to have that AutoNumber for each table. So I'm going to put in here stateZoneID. That would be my AutoNumber. Yes, I recommend an AutoNumber for each table. Why? I'll put a link to a video below in the description. Watch that video. People question me all the time, are AutoNumbers really necessary? Yeah, I kind of think they are. I've been building databases with Access for 26 years now and I pretty much always use an AutoNumber, with very few exceptions.
Next we'll type in state and that'll be short text. This particular business only does business in the United States. So I can get away here with making that 2 characters. But if you want to leave that bigger because you do business in other countries, that's up to you. For this particular database, the state size only has to be 2. I'm also going to index that, no duplicates, because I don't want New York listed in here twice, for example. I want to look up the state and it will bring back a zone. So the zone is a number.
Save this. This will be my stateZoneT, T for table. AutoNumber, here you go, primary key. Now I can fill in some data. Let's go to datasheet view. I'll just put a couple in. Let's say New York is zone 1. So is Pennsylvania. So is Maine. Florida is zone 2 along with South Carolina and Georgia. We'll do California as zone 3 by itself. And then one more, maybe Texas is zone 4.
There are my zones. So a new customer comes in from New York. The system will look up zone 1 and put that in the customer table in that zone field. I couldn't just relate this to the state field in here because again, you can't change it then. Not everybody from New York will be in zone 1. It's just a default value.
So how do we get that in the customer form? Let's first put the zone in the customer form. Right click, design view. Let's put a text box up here to have the zone in it. You can go up to add existing fields if you want and find the zone field. Click and drag, drop it right there. Slide it over here. Maybe move the label up next to it.
Save changes (Control+S), and we'll close that and then reopen it. So we've got a zone here. Now this zone field has no data in it. If you want to, you can open up the customerT and you can put zones in. I'll just leave them blank for now.
What I want to happen is when I type in a new state down here, I want to go out to the table (the stateZoneT that we created), look up the zone based on that state, and then put a value in here. How do I do that? I'm going to use something called an AfterUpdate event. AfterUpdate says when I change the value, when I update the value in a particular field like a text box, do some stuff. What is that stuff? It could be either a macro or some VBA programming. I personally prefer VBA programming. It's not that hard once you get the hang of it, and I'm going to show you exactly what you need to know.
Right click, design view. Let's find the field that we want to put the event in. There it is right there. You see zip, we want state, double click on it. That brings up the property sheet. On the Event tab, find After Update right there. Hit the ... button over here. That's the builder button. It may ask you which builder you want. We want the code builder. We don't want macros or expressions. We want the code builder.
That opens up the Visual Basic window. I've got some other code in here from previous lessons. These open different forms inside of buttons. But this is what we want right here: State_AfterUpdate. This is the event that runs after I change the state field. Let's put something simple in there. For example, let's just put in message box "Hello World".
Now let's go to the typical first programming example. Message box just throws up a box on the screen with a message in it, and it'll put up "Hello World". Save that. Close the Visual Basic Editor. Let's close the form and reopen it. Now come in here and let's change Florida to Pennsylvania. As soon as I hit tab, look at that. "Hello World" runs because I changed that field, that value, and then the AfterUpdate event ran. It runs whenever you change a particular field.
Message box just says "Hello World". You hit OK and you're right back, editing data in the form. Now I don't want it to say "Hello World". I want it to look up that zone. So we have to use a particular function called DLookup.
Let's go back to design view. Go down to the state field again. Bring up its properties. Go back into the AfterUpdate. ... puts you right in here. Now instead of message box, we're going to use the DLookup function. We're going to say zone = DLookup.
Now DLookup takes three bits of information, all inside quotes. The first thing is what is the field that you're looking up? Well, I'm looking up the zone field. The domain, the second thing, is what table or query are you looking up this data in? Well, it's stateZoneT. You can see it right over there in the navigation pane.
Comma, what is the criteria? In other words, it's a WHERE condition. So here I'm going to say where [state] equals. Here's the tricky part. This has to be inside quotes. It's got to be single quote, double quote, ampersand, state, ampersand, double quote, single quote, double quote. I know it's confusing. I have a separate lesson on string concatenation. That's what this is when you put two strings together. Go watch that video if this is confusing to you. Do not feel bad. It confuses a lot of people. This is one of the things that I get asked all the time. That's why I made a separate video on it.
Basically, if you take a look at it, you need to say [state] = 'New York' like that. So state is a field on the current form. It's also a field in that table. So it's the same thing, but it's two different fields. But [state] = 'New York', the New York has to be inside quotes because it is a string itself, but that whole thing has to be inside quotes. You can't just do this, because this in turn would close that string there. See, it's confusing.
So what you have to do is you have to make these single quotes that are inside of double quotes. Or you can make them double-double quotes. I explain all of this in the concatenation video.
Then we've taken this out of the middle and made this part here a field on the form. So yes, string concatenation is kind of crazy. Watch that other video. It's 20 minutes long and it explains string concatenation in a lot more detail. You kind of have to know it to work with the lookup and AfterUpdate, unless you're just using IDs and then you don't have to bother with that.
Anyways, what this says is look up the zone in the stateZone table where the state equals whatever the state is on the current form, and set it in the zone field. A little confusing, I get it. This makes more sense after you've done it a couple of times.
Let's close this. Save it, open it back up again. Now let's put me back in Florida. Boom. And look at that. I'm in zone 2. See that? The AfterUpdate event ran. It looked up Florida in my stateZone table, found the state, brought back the zone field, and put it right there.
If you're familiar with Microsoft Excel, this is very similar to the VLOOKUP or the new XLOOKUP function. I have videos on those too. Watch those. They'll give you a better understanding of what DLookup does. D stands for domain. In Excel, you have VLOOKUP, where the V stands for vertical, and there's HLOOKUP, which looks up horizontally. Then they made up an XLOOKUP function, which is new. It just came out this year, and it looks up all kinds of different stuff. They probably picked X because it was cool. But DLookup stands for domain. In Microsoft Access a domain can be a table, a query, all kinds of stuff, so that's why it's DLookup.
I'll put links to those XLOOKUP and VLOOKUP Excel videos down below too. Those are pretty good. Watch those if this seems confusing at all. In fact, I have another video that also uses lookups. It's a zip code lookup video. I'll put a link to that down below as well. That'll show you how to look up a zip code. You enter the zip code and it pulls back the city and state. Same basic concept as this.
Ready for another one? Now we have some Riverside, well, that's fine. Now he's going to be from Texas. Boom. Zone 4. This guy's from New York. Boom. Zone 1. That's how the AfterUpdate event works.
Now what happens if you type in a state that's not in there? What if I type in, let's say, Washington? It goes to null. Wouldn't you like it if the code could give the user a warning message that says, hey, the state wasn't found. Would you like to enter it now? We'll cover that in the Extended Cut members edition.
The members only Extended Cut edition covers prompting for that state. If you type in a state like Ohio and it doesn't exist, Access will prompt you to enter it. It says, "Ohio is not found in the zone table. Please enter a zone." Type in a number, then we'll save it to that zone table. So the next time you do a lookup, it's just in there.
We'll talk about a bunch of different concepts, including InputBox, the NZ function, converting to integers, the IsNumeric function, and I'll show you some SQL to add that record into your table.
Here we are. Type in Ohio, tab. It says, "Ohio is not found in the zone table. Please enter a zone." I'll give it 14, and then it puts the 14 in here and saves it in my zone table right down there: 14.
We'll go over some different stuff in case your users try to get funny. So if they type in something like Tennessee and they hit cancel, it just leaves it. Or if they try to put in something like garbage, it will yell at them and say it must be numeric. So we'll talk about some of those different things. Lots of little nuggets for my members in this extended cut video.
How do you become a member? Click on that Join button down below the video on my YouTube page. Silver members and up get access to my extended cut TechHelp, plus live video chat sessions and lots of other perks. Click on the Join button and you'll see a list of all the different options and the perks that each level gets.
But don't worry, my TechHelp videos are free and they will always be free. I love bringing these videos to you on YouTube. Make sure you subscribe to my channel. Again, that's absolutely free to subscribe. You'll get notifications if you click the bell icon whenever I release a new video.
Also, feel free to stop by my website and check out my Access forum. Make sure you check down below the video. Click on that Show More link. I know a lot of people mentioned to me that they had no idea this was down there. YouTube does a pretty good job of hiding it. If you click on that Show More link, it will bring you to a whole bunch of other resources that I have.
I've got related lessons. For example, in this lesson, I talk about concatenation. I've got other videos on concatenation, XLOOKUP, VLOOKUP. I'll put links all down below the video there that you can follow to learn more about the topics from today's class.
One of those links will be for my Access Level 1 class. It's a free 3 hour long tutorial that covers all the basics about Microsoft Access. So if you have any questions, they're usually covered in that as far as the beginner stuff goes. If you like Level 1, Level 2 is just $1. That's another full hour long class.
If you've got questions you want to see answered in a video like this, visit my TechHelp page. There's all my other cool stuff: Facebook, Twitter, my blog.
That's it for today. I hope you learned something. We'll see you next time.Quiz Q1. What is the main purpose of using the AfterUpdate event in this video?
A. To validate data before it is saved in the form
B. To automatically look up and fill in a field when another field is updated
C. To prevent duplicate records from being entered
D. To export data to Excel after changes
Q2. Why is using a relationship between the state and zone tables not suitable in this scenario?
A. It is more secure not to use relationships
B. Relationships slow down the database
C. Because not every customer from a given state must be in the same zone
D. Relationships require every table to use AutoNumbers
Q3. What is the function of the DLookup function as described in the video?
A. It validates email addresses in a table
B. It looks up a value from a table or query based on specified criteria
C. It sorts a table alphabetically
D. It deletes records that match certain criteria
Q4. In setting up the stateZoneT table, why does the instructor recommend using an AutoNumber as a primary key?
A. Because it is required for all tables in Access
B. It ensures every state is linked to a unique zone only
C. It makes each record unique and helps in managing data integrity
D. It allows direct relationships between all fields
Q5. What does string concatenation refer to in the context of the DLookup criteria?
A. Combining numbers with arithmetic
B. Concatenating two database fields into one output textbox
C. Building a string expression with variable values and delimiters
D. Formatting field labels on forms
Q6. What happens when a user enters a state in the customer form that does not exist in the stateZoneT table (according to the standard version of the code)?
A. The field is automatically filled with zero
B. Access assigns the state to the first zone
C. The zone field is left as null
D. An error message appears immediately
Q7. Which of these is the correct general structure of the DLookup function as used in this video?
A. DLookup("[Zone]", "[CustomerT]", "[Zone]=1")
B. DLookup("Zone", "stateZoneT", "[State] = 'state_value'")
C. DLookup("[State]", "ZoneT", "[Zone] = 'Zone_value'")
D. DLookup("Zone", "CustomerT", "Criteria")
Q8. If a user types in a valid state, how does the zone value get set on the form?
A. The zone is manually entered by the user after adding the state
B. The AfterUpdate event of the state field runs code to look up and assign the correct zone automatically
C. The stateZoneT table automatically updates the zone for all customers in that state
D. It is set through a form relationship property
Q9. How is Access DLookup similar to Excel functions as described in the video?
A. It filters records like Excel's autofilter
B. It is similar to Excel's VLOOKUP or XLOOKUP for retrieving values
C. It formats cells the same way as Excel conditional formatting
D. It automatically generates charts
Q10. What should you do if you want to allow a user to override the default zone value that is looked up?
A. Make the zone field locked so users cannot change it
B. Set zone as a calculated field
C. Leave the zone field editable so the user can override the value after it is assigned
D. Use a relationship that enforces referential integrity
Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-C; 7-B; 8-B; 9-B; 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 video from the Access Learning Zone focuses on how to use the AfterUpdate event and the DLookup function in Microsoft Access to automatically populate fields in a form. One of the most common scenarios with customers is needing to assign a zone based on their state. However, sometimes exceptions occur, such as a sales representative bringing in a customer from a different zone, so having a default with the flexibility to adjust is important.
Let me walk you through how to set this up. If you want Access to automatically fill in a default zone when you enter a state for a new customer, but also allow for changes when necessary, you need to do this with a combination of table design and some VBA.
First, we start with our basic customer table and form. The customer table needs a new field for the zone. I add this by opening the table in design view and inserting a field called 'zone' as a number data type, using a long integer. That takes care of the customer side.
Next, you need a lookup table that will match up states and their default zones. This table, which I call stateZoneT, includes an AutoNumber field as the primary key – stateZoneID – a two-character state field set to no duplicates, and a numeric zone field. Using AutoNumber as a primary key is a best practice, even if you do not specifically use it for relationships. It keeps each record unique and maintains database integrity. If you want to know more about why AutoNumbers are important, I have a separate video on that as well.
Once the lookup table is set up, I fill it with data. For example, New York, Pennsylvania, and Maine might all be assigned to zone 1. Florida, South Carolina, and Georgia can be zone 2. California might be in zone 3 and Texas in zone 4. The point here is that a state can be assigned to any zone, and this lookup lets you specify the default.
Simply relating the state in the customer table to the state in the zone table would not work, because you want to be able to override the default if needed for each customer. Instead, we use a process to look up the zone when the state is entered in the customer form, and save it directly in the record.
To do this in the form, I add the zone field so users can see and update it as needed. Now, the key is to automate the process so that as soon as the state is entered or changed, the database fills in the zone for you. Microsoft Access provides the AfterUpdate event for this purpose. It triggers code to run every time a user updates the value in a field – in this case, the state field.
Setting up the event handler is straightforward. Open the form in design view, select the state field, and open its property sheet. On the Event tab, find After Update and choose to create a Code Builder event. This brings you into the Visual Basic editor where you can insert some code that will perform the lookup.
To confirm everything works, I often use a simple message box as a first step. Writing a message box that says "Hello World" lets you confirm that the event fires at the right time. As soon as you change the state and press tab, the message box pops up, indicating the event is set up properly.
With that confirmed, the next step is to replace the message box with code that uses the DLookup function. DLookup is used to look up a value in a table or query – much like VLOOKUP or XLOOKUP in Excel. The DLookup function takes three main arguments: the name of the field you want, the table or query you are searching, and the criteria for the lookup.
The lookup is structured to find the zone in stateZoneT where the state matches the state just entered in the form. This does require a bit of string concatenation, since the criteria string needs to be constructed dynamically. If you find string concatenation confusing, I recommend you watch my companion video on that topic.
Once the DLookup is set up correctly, whenever you enter a state in the customer form, Access finds the right zone from the lookup table and fills it in for you. This works for states already predefined in the list. You can override the zone if needed since it is stored on the customer record individually.
One common question that comes up is what happens if you enter a state that is not in the lookup table. In this case, DLookup returns a null value, and the zone field remains empty. If you want to prompt the user to enter a new zone for an unknown state and then save that in the zone table for future use, I have a members-only Extended Cut that covers exactly how to do that. In the Extended Cut, I cover how to prompt the user with an InputBox, validate the input, and, if appropriate, update the lookup table automatically. I also go over some extra tips, like handling unexpected input, checking for numeric values, and adding new records using SQL.
If you are interested in accessing these extra features and lessons, you can become a member on my website. Silver members and above get access to all Extended Cut TechHelp videos, live chat sessions, and more. But rest assured, the main TechHelp videos will always remain free for everyone. Just be sure to subscribe to my channel so you do not miss new updates, and check out my website where you will also find my Access forum and more resources.
If you are new to some of these concepts, especially DLookup and string concatenation, you will find dedicated lessons linked below. I also have full-length free introductory classes on Microsoft Access for beginners, and Level 2 is available for a nominal fee if you would like to keep learning.
If you have questions you want to see answered in a future video, be sure to submit them on my TechHelp page. You will also find links to my blog, Facebook, Twitter, and more down there.
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 a zone number field to the customer table
Creating a state-zone lookup table
Setting up fields and primary keys in the lookup table
Entering sample state-zone data
Adding the zone field to the customer form
Configuring the AfterUpdate event for a form field
Using VBA to handle the AfterUpdate event
Demonstrating use of the DLookup function in VBA
String concatenation in DLookup criteria
Testing automatic zone assignment based on state input
Explaining the similarity between DLookup and Excel VLOOKUP/XLOOKUP |
