ZIP Code Lookup
By Richard Rost
6 years ago
How to Perform ZIP Code Lookup in Access
In this Microsoft Access tutorial, I'm going to show you how to perform a ZIP code lookup. You will be able to enter a ZIP code and have the system return the city and state for that ZIP code (yes, works with most foreign countries too). If it doesn't exist in the database, you will be prompted to add it.
Members
There is no extended cut for this video, however there is a more advanced version of this database available for purchase in the Templates section.
Database File
Advanced Template
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, zip code lookup
Intro In this video, I will show you how to set up an automatic zip code lookup feature in Microsoft Access. We will talk about creating a zip code lookup table, setting up your customer form to auto-fill city, state, and country fields based on the zip code entered, and letting users input new locations if a zip code is not found. I'll also discuss design choices for normalization, how to use VBA code events like AfterUpdate, and how to add new records to your lookup table using SQL and VB.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's lesson, we're going to talk about zip code lookups. Now, this is where you can type in the zip code and the system will automatically populate the city, state, and country if it's in your database, and if not, it will prompt you for it. This helps keep your database nice and neat by giving you the proper spellings for the cities and states and so on.
It'll allow users to enter new ones if they don't exist in the system already, but it also allows you to freely input the city and state if you want to. So let's take a look at the question for today.
This actually started as a thread in our Microsoft Access Learning Zone discussion group on Facebook. Brent, I know you had one of our questions previously. I don't mean to keep picking on you, but this is actually a topic that I thought about covering earlier.
Brent asks if it is better to build your tables so there is an area, a city table, a state table, and a company table. He probably meant a country table, so you're getting as few keys to input as possible from the user. I just wanted to get your thoughts on this as I'm always trying to limit misspellings and duplicate information.
Yeah, that can be a problem if you have people spelling things differently, like cities, for example. You have people putting in the same zip code, but they spell Los Angeles wrong or something along those lines.
Robert added that there's ZipFirst, which has a zip code table. There are a lot of different sources out there that you can use to download zip code databases that you can actually pull into your database that has every zip code in the country, and you can just simply look that up and then pull in the city and state. Again, you run into a problem if you have foreign customers, but if you're just doing business in the US that'll work.
Anthony brings up, "What if someone deletes Massachusetts and that happens to be ID 7 in your table now?" Of course, you've got sevens. I don't like linking states as an ID. I'll talk about this in a second.
Stefan correctly notes, don't use autonumbers as a piece of data in the table.
Then, of course, there's my comments. I like having a zip code table with matching city and state names. However, I don't like saving those as IDs in the customer table because there are some instances where a particular zip code might encompass multiple towns with different names, and you want the user to be able to freely edit that once it's been typed in. So, I like to give them an option to pick the city from a list, but don't necessarily force them to use it.
Now, here's what I mean about a certain area having multiple towns inside of it. For example, here where I live in Florida, I live in Fort Myers. If I type in 33966, which is my zip code, you can see that that's just inside of Fort Myers. However, where I grew up was 14075. Now, 14075 is in Western New York, but it includes multiple townships. So if you have that zip code, you could be from Hamburg, you could be from North Boston, and those would show up on your address. You are a Hamburg resident, you're a North Boston resident, but you're in the 14075 zip code.
So that's part of a problem, but we can address that by just simply giving a default option as Hamburg, and then someone can type in North Boston. Yes, you could do a table that you can pick from multiple cities and states, and I'll cover that in different lessons. But for today, I'm going to show you the basic one where you can have a single default.
So rather than reinventing the wheel and rebuilding a customer database with a customer table and form, go to my website real quick and grab my customer database template. It's free. It's got a basic customer table and customer form already set up. There's the web page. Here it is, customer database template. Scroll down a little bit, click on the download button.
All right, save it to your desktop or wherever you want to put it, open it up. If you get the security warning, go ahead and click enable content. You don't have to--there's no code. I don't think there's any code that runs in this database so far, but trust me, it's safe.
All right, we've got a basic customer table now. This is how I'd like to have the customer table still set up even with the new things we're going to do. We've got city, state, zip code, country, and they're all text fields. This way, the user can still come in here and make changes if, for example, they put in 14075 and it's not Hamburg, they want to put in North Boston, they can still make that change.
Now, if you decide later on to add a lookup for multiple towns, you can lock these fields. All right, you can set it so they can't freely type stuff in at the form level or even at the table level, but I would recommend leaving it as freely entered text. Is it perfectly normalized? No, but there are sometimes excuses why you wouldn't want to do that and this is one of them.
Just like when I talk about making customer orders, you don't necessarily want to have the customer's address tied to their order with a relationship, because if they move, you want to know where the order was originally shipped to, not what their new address is. So this is one of those situations where 100 percent completely normalizing the database doesn't make sense. There are a lot of exceptions; this is one of them.
All right, so the customer form's pretty simple. Now what I want to do is I want to have it so that when I'm tabbing through here, I'm going to come to zip code first. All right, if the zip code field is blank and they enter something, look and see if city and state are blank, if so, pull up that information from a zip code table if it's in there. If it's not in there, then prompt them for it and add it to it.
So let's rearrange the customer tables. Go to design view, and I'm going to do this. I'm going to move everything down a little bit like this, and I'm going to put zip code up here. Make it a little bit bigger, make it a little more prominent. Let's copy one of these labels. I'm going to copy that and then paste it on top of zip code; that attaches that label. All right, and we'll put it here: zip code, like so. If you want to add flash or color, you can, just to kind of remind the user that something happens. I like to do that. Let's open up this menu here. Let's put a splash of color behind it, maybe like a little bit of yellow, just so it reminds them that something's going to happen.
Now, we need to build a lookup table next. So let's save this form, close it, and let's create table design. All right, our ID--doesn't matter what it is--we're going to need a zip code, short text, city, state, and country. I like putting country in there because it'll be compatible with places like Canada and such. Save this as my ZipLookupT. Primary key defined? Yes, okay. You can call this zip lookup ID, whatever you want to call it. All right, we're not going to really use it.
Let's put some sample data in the lookup table. All right, I'm going to put my current one in there: 33966. City is Fort Myers, Florida. I'm going to leave country blank for USA--that's my default. You can put multiple ones in here if you want to.
All right, let's go back to our form now, design view. Now, when this guy is updated, take a look and see if city is null. You could check city and state, but I'm just going to look at city. You're going to have city in there, then that will trigger the update; if not, then we won't.
So we're going to need an after update event for this zip code. Now, if you don't know what the after update event is, it's when you update or change a field--a text field or whatever--and some kind of programming event fires. Yes, this example is going to require a little bit of programming. I'm going to show you what you need to know.
I have another tutorial on YouTube, the after update event, that walks you through the after update event in a little more detail if you've never seen this before. So go watch that right now if you need to, and then come back.
So, right click on the zip code, go to properties, way down here at the bottom, properties. Property event comes up, go to events, after update, click on the dot dot dot. You might get another window up at this point asking you what kind of builder you want--you want to pick the code builder. You want to build some VB code. I have the option set in my database as the default. It's a pain to have to answer that every time you're doing something. So pick code builder. That will bring you into the Visual Basic editor, and again, you might have some stuff over here on the left. Ignore that.
Now, at this point, you could check to see if there's already a city and state in there, because you might want to have it where they don't automatically overwrite the city and state. You could say something like, "If Not IsNull(City) Then Exit Sub," and that will exit out of here if there already is a city. I'm going to leave it--I'm actually going to get rid of that, because I'm going to have it so if they type in a new zip code, it overwrites what's there. Right? If the customer changes, you want to put in the new city and state.
So, let's just do a little test at this point and see and make sure we got the zip code. So I'm just going to message box the zip code, just like that: MsgBox Zip. Save that. Flip back over to Access, just down at the bottom of my toolbar. I'm going to close the customer form and reopen it, and I'm going to just change this: 33977, for example, press tab or enter, and it pops it up--there we go. So we've got the zip code in our form. That's already saved. All right, beautiful.
Let's go back over to our VB editor. Notice how you can just switch back and forth between them down here on the taskbar, or you can hit the code button if you're in design view, but I like to leave the Visual Basic editor open.
So, what we want to do next is look up and see if that zip code appears in our zip lookup table. If it does, automatically fill in the information from the city, state, and country fields.
All right, so let's get rid of this stuff here. Let's put something new in here. We're going to look up that zip code in the zip lookup table and see if it exists, and if it does, then we will pull in the information from the city, state, and country fields.
So, let's use DLookup to grab the ID from that table. So I'm going to say: Dim ID As Long (a long integer), and then I'm going to say: ID = DLookup("ID", "ZipLookupT", "Zip='" & Zip & "'")
A lot of concepts here that you may or may not be familiar with. I've got tutorials for all this stuff on my channel already, but I'm not going to go over it here. There's DLookup and then there's string concatenation. Let me give you some links real quick. I'll put these links down in the description below so you can just click on them. But DLookup is what you use to look up a value from another table. String concatenation is putting together two strings, like first name and last name, into the same field. I just finished a TechHelp video on string concatenation that talks about those double double quotes--all that stuff around the zip code field.
So what this is saying here in a nutshell is this ID field is going to be set equal to the ID from the zip lookup table where the zip on that table is the same as the zip in this table on the form. All right, this is the value from the form and it gets sent to the DLookup function. So this should return the ID for whatever is in that table.
So let's take a peek at it, make sure we got it: MsgBox ID right here. I like the message box often to make sure I've got the right values.
Yes, I've got tutorials on my website to cover all the "Dim" stuff, the Visual Basic beginnings, the message box--it's all on my website. I can't go over it in every lesson, but message box is what you use to put something on the screen.
Back to Access. Let's change this slightly: 33966 I know is in the database already, and there's the number one. That's the ID from the record in this table. All right, this is a lookup table; that ID gets returned.
Now, what happens if it's not in there? What if I type in 3360? All right, I get "Invalid use of null". That's an error message meaning it tried to return something that doesn't exist, so I'm going to hit End. Now, you can get around that error by using an NZ function called null-to-zero. All right, NZ will basically say, "If this DLookup returns a null value, if it doesn't exist, then you can specify what value to give it." So I'm going to give it zero. In other words, if that ID doesn't exist in the table, return zero. No ID can be zero because autonumbers always start at one and go up. You can also use ID with text strings to return, like, a blank string or any other value that you want. That's what the second value is for right here--that's NZ, null-to-zero. And again, I cover that in my classes.
All right, so let's see what the message box returns now. All right, if I type in something that doesn't exist, like "3", I get a zero. Okay, so now we just have to check and see if we have a zero or some other value.
So right here, I'm going to say, "If ID > 0 Then," we are going to look up--let's say, zip code exists, look up data. Else, zip code does not exist, ask for it.
The first part is relatively easy. All right, right here city = DLookup("City","ZipLookupT","ID=" & ID). This is inside of a text string, so Access doesn't do that pretty little thing where it automatically capitalizes stuff for you. I like to make my code look clean, so if I accidentally put an "o" in there like that, I want to fix that. All right, even though it'll work because Access is not case sensitive, it still looks prettier like this. And here, we don't need the extra quotes in here because ID is a numeric value.
Now, I can just copy and paste this--copy, paste, paste--for state and country. All right, and then we're done. That's the "if it exists" part. Let's go ahead and test it.
All right, 33966--boom, Fort Myers, Florida popped in there. If this stuff was blank and I come in here and I put in a 5, nothing happens. If I put in the 6--boom, it fills those fields in.
Okay, now I can still come in here and edit this stuff. These are freely editable. You can lock these if you want to--just lock the text boxes, set the locked property. But I like to leave these editable for the reason that I mentioned earlier.
Now, what to do if we don't have that value in the table? We have to ask for it and edit. So let's get three more memory variables:
Dim sCity As String, sState As String, sCountry As String
We're going to ask the user for these three values. We're going to say:
sCity = InputBox("Enter city", "Zip code not found")
sState = InputBox("Enter state", "Zip code not found")
sCountry = InputBox("Enter country", "Zip code not found")
All right, let's just make sure this works so far. Let's come down here and put something in that doesn't exist. The input box pops up, you put some stuff in it, enter state pops up next, then country. Canceling any of them just stops. So that's working so far. All we've got is to pop up those input boxes.
Now, we've got these values in memory variables, all right, stored up here in these string values. Now we're going to add them to the form itself. All right, so let's just put them in the form, then we'll add them to the table. That's the first part--it's easy.
City = sCity
State = sState
Country = sCountry
All right, let's test that and see if that works. Notice what I do here, by the way. I do a little bit and then I test it instead of writing a ton of code; then you don't know where it's not working. Even I, who have been programming since I was about eight years old, after getting close to 40 years there, don't want to write a ton of code and have to debug it all at once.
Let's go back over here and make sure this works. All right, let's put something in here that's not in the table. Enter city, let's say city is "Somewhere", state is "Florida," and the country will be blank. Boom, they put "Somewhere, Florida" in there.
So now, getting the values and putting them in the form is the easy part. Now, putting them into the table can be a little tricky unless you know SQL. If you know SQL, then it's real simple. It's one line of code, which I'm going to turn into two lines of code so I can use a variable for it. So, a little bit of SQL here.
Dim SQL As String
We're going to put some SQL statement in here now. I'm going to use an SQL INSERT statement. Here are two more lessons for you to check out if you don't know anything about SQL (Structured Query Language)--watch this one. And if you don't know what an append query is, where you add records to a table, then watch this one--an append query can take some data and put it inside of a table for you.
Now, if you aren't a SQL genius and you don't always remember the syntax, if you don't use it every day, you don't remember it. Personally, I remember SELECT statements--those are easy--but I don't remember all the other ones. So you can actually have Access design this query for you and just copy the SQL. I do this all the time.
So: Create, Query Design--I'm going to shut the navigation pane for a minute, we don't need that, or the property sheet. What we're going to do is make this an Append query. What table are we appending onto? We're appending onto the zip lookup table--hit OK. What tables are we going to pull data from? We're not pulling from any table, so don't worry about that stuff here. So down here, we're going to actually put values into the table from our VBA code--the stuff that we just asked for. We're not pulling data from a table.
All right, so what fields are we going to be appending into? Come down here to the "Append To" field--we're going to append the stuff that we just got. We're going to append to "City." What is the data? Well, the data is going to be whatever I collected from my VBA code, so just put in here "ccccc" for city. Now look, Access translates that into "Expression1: ccccc." Do the same thing for state--a bunch of s's, then country--we'll put in just some y's. All right, now we also have to put the zip code in, so put in a bunch of z's because we just collected that as well.
Now, we're not going to run this guy; we're going to take a look at its SQL. So right-click here or go up to the View button and pick SQL View. This is the code that we need to put into our VBA, so copy this stuff--the INSERT INTO command. It's called an append query, and in SQL it's an INSERT. All right, copy, come back over to VBA, left it open in the background down here.
All right, and right here I'm going to say,
SQL = "INSERT INTO ZipLookupT (City, State, Country, Zip) " & _
"SELECT '" & sCity & "', '" & sState & "', '" & sCountry & "', '" & Zip & "'"
Insert city, state, country, and zip.
Now let's take a peek at my SQL statement just to make sure it's correct. So MsgBox SQL and we'll see what we got, we'll see what this turned into.
All right, we can close that query, we don't need to save it. Let's put something in here, e.g., 14222.
Enter city: Buffalo
Enter state: New York
Enter country: (leave blank or USA)
There's my SQL statement that we generated:
INSERT INTO ZipLookupT (City, State, Country, Zip)
SELECT 'Buffalo', 'New York', '', '14222'
Hit OK. Now we have to actually run that SQL, which is really easy now:
DoCmd.RunSQL SQL
And then you can put a message up here that says, you know,
MsgBox "Zip code added to lookup table"
if you want the user to see that.
All right, we should have a working solution now. Let's go test it.
Here we are: zip code 14075, Enter. Zip code not found: "Hamburg," "New York," country is blank, Enter. Zip code added to lookup table. Let's go see: ZipLookupT--oh, there it is, beautiful.
Let's do another one. Let's do 33993, Enter. Zip code not found: "Cape Coral," "Florida," "USA" boom. Zip code added to lookup table. Let's double-check--oh, there it is, pretty cool.
Let's do one from a different country. Let's do M4B (now see, with Canada they put a space in the middle), so you have to decide now if you want to limit it to just those six characters or if you want to add that space to the center. I don't know a lot about Canadian zip codes. I actually just looked this up on Google; I'm going to make it so there's no spaces in there. So M4B, and you can actually filter out the spaces if you want to keep your zip code lookup tables clean.
All right, let's put that in there. That's Toronto, state will be Ontario (the province), and then Canada.
All right, zip code added to lookup table, boom. Let's double-check--looks good.
All right, what if we go back to 33966, Fort Myers, Florida? If you don't want spaces in your zip codes, then when it's looked up, the first thing you can do right up here is:
Zip = Replace(Zip, " ", "")
That'll say, take every space character in the zip code field and replace it with a blank--so it just gets rid of all the spaces.
So now if you do get someone that types it in with the space, it should still correctly find it, so M4B... Actually, before I do that, I want to capitalize everything, too. Let's real quick, while we're at it, say:
Zip = UCase(Zip)
That'll capitalize it first before doing any lookups or adding it to the table. That way your table is nice and clean.
All right, one more try. All right, what was that zip code again? What did I use? M4B, and then they put a space in there--1B3. See how it corrected it and then it looked up Toronto, Canada? And you could theoretically do 14075 with a space and it'll fix it.
That's making it smart.
Okay, that's all for today's class for the TechHelp question. You have enough information to go forth and do what we were trying to do; we've accomplished our goal.
However, I did make an extended version of this video available. It's on my website. What the extended version does is it takes into account a situation where a zip code may have more than one city and state associated with it.
Now, I grew up in such a place--as you can see on the screen there, zip code 14075 encompasses multiple cities and multiple towns. I grew up in Hamburg; there's also North Boston and a couple other ones. So if someone types in 14075, the current system that we have now--which works--will just always bring back Hamburg, and if it's something different you'll have to type it in.
What if you want the user to be able to pick that option or add a new one? Well, that's where this comes in.
In the extended version, if we type in a zip code--let's say 88888 just for whatever--if it doesn't exist you can see that we're prompted immediately to put in something. Let's call this "My Town," "Florida," and then we'll hit Set. That sets the value in here.
Now, if on another record, let's just go here and say this guy is 88888, it automatically puts in "My Town." But if that's not right, you can hit Edit and add something else--"My Town," "Your Town," "Florida," and then set that one.
Now, if the next person comes in and uses 88888, you're prompted for which one it is. Are you in "My Town" or in "Your Town?"
So it handles all three situations: if it doesn't exist you're prompted for it, if it does exist and there's only one, it'll just fill it in for you (which is the vast majority of them), or you can go back and edit it if you want to put a different city and state in there.
Of course, you can just come in here and just type in manually. I like giving the user the ability to type in manually without adding it to the database as well.
So if you want the extended version of this database, you can find that on my website. There's the link right there. I'll also put it in the description field below the video, as well as links to all of the other stuff that we talked about in this lesson, including the DLookup function, string concatenation, after update events, SQL basics, append queries--all kinds of stuff covered in this lesson.
Thanks, and I hope you learned something today.
Stop by and check out my template section. I've got lots of other templates available on my website.
If you like what you saw today, make sure you subscribe and ring the bell--click on the little bell to get notifications. I'm going to start doing live streaming soon, too, where I can answer your questions live on the air. So if you subscribe you'll get a notification when I go live.
Want your questions answered? Visit my TechHelp page and you'll see how you can submit your questions. I've also got a pretty active group on Facebook and you can submit your questions there. This is where this question actually came from. I've also got some forums on my website available as well--you can find them at the forums link there. I recently put some time into fixing my forums so they work again. Drop me an email if you want.
And there's your links to my goodies: Facebook, my blog, Twitter, YouTube, all that stuff--shameless advertising.
And of course, with every lesson, you gotta watch a little advertising. Right: Level 1, three hours long. There's a link. Did I mention it was three hours long? If you like that, Level 2, which is another hour and something, one dollar.
All right, I hope you learned something. Thanks for watching. Now go forth and get to work on Access.Quiz Q1. What is the main purpose of implementing a zip code lookup system in your Access database?
A. To automatically populate city, state, and country fields based on zip code input
B. To prevent users from adding new entries to the database
C. To limit users to only U.S. addresses
D. To encrypt the customer's address information
Q2. Why is it a problem to allow users to freely type city and state information without control?
A. Users might select the wrong country
B. They may create duplicate orders
C. They might introduce misspellings and inconsistent entries
D. It will increase file size significantly
Q3. What is the suggested method for handling situations where a zip code covers multiple towns or cities?
A. Always force a single default city for each zip
B. Allow users to overwrite or edit the city field if necessary
C. Only provide a pick list and prevent free text entry
D. Do not record city and state data at all
Q4. Why should you avoid linking states or cities as IDs in your customer table?
A. IDs are inefficient and slow down searches
B. There is never more than one city per zip so IDs are unnecessary
C. Autonumber IDs might change if records are deleted, breaking references
D. IDs cannot be displayed to users on the form
Q5. What is the purpose of the After Update event in Microsoft Access forms?
A. To recalculate all totals on the form
B. To trigger an action when a field is modified by the user
C. To save the form data to the database automatically
D. To lock all fields for editing
Q6. What function is used in VBA to look up a value from another table?
A. DSum
B. DCount
C. DLookup
D. DSQ
Q7. If a zip code is entered but is not found in the lookup table, what does the system do?
A. Automatically blocks entry and prompts an error
B. Requests the user to input city, state, and country information
C. Fills the fields with default values
D. Deletes the current record
Q8. What is the purpose of using the NZ function in the context of DLookup?
A. To normalize strings to upper case
B. To replace a null return value with zero or another default
C. To append new records to the table
D. To save form data
Q9. What VBA method is used to execute an SQL statement that adds a new zip code record to the lookup table?
A. DoCmd.Close
B. DoCmd.ShowSQL
C. DoCmd.RunSQL
D. DoCmd.ExecuteInsert
Q10. Why does the instructor recommend leaving city, state, and country fields as editable text instead of locking them?
A. It reduces code complexity
B. It ensures perfect database normalization
C. It allows flexibility for special cases and user corrections
D. It enables automatic data validation
Q11. What does the Replace function, as used in the lesson, accomplish with the zip code string?
A. Converts all letters to lower case
B. Removes spaces from the zip code
C. Appends a city name to the zip code
D. Removes numbers from the zip code
Q12. Why does the instructor convert zip codes to upper case before saving or searching?
A. To save database space
B. To standardize formatting and ensure consistent matches
C. To hide the zip code from unauthorized users
D. To encrypt the data
Q13. What technique is used to generate the correct SQL INSERT statement when unsure of the syntax?
A. Guess by trial and error
B. Use Query Design to create an append query and view its SQL
C. Copy from another form
D. Download from the internet
Q14. If you want to allow having multiple cities for a single zip code, what does the extended version of the database offer?
A. It locks the fields so no changes can be made
B. It automatically picks the most populous city
C. It lets the user choose from existing options or add a new city for that zip
D. It prevents duplicate city entries
Q15. In the basic zip lookup system demonstrated, what happens if the user enters a zip that already exists with one city in the lookup table?
A. The form prompts the user for city and state again
B. The form autofills city, state, and country using the matching lookup record
C. The form deletes the previous record
D. The form locks the city field
Q16. When discussing database normalization and addresses, why did the instructor suggest that full normalization is not always practical?
A. Access does not support normalization
B. It is sometimes necessary to allow edits for special cases like moving addresses
C. Normalization can only be used with SQL Server
D. Normalization is never useful in Access
Q17. What happens in the basic version if a user enters a new zip code not previously in the lookup table?
A. The user cannot save the record
B. The system asks the user to input city, state, and country and then adds it to the lookup table
C. The fields remain blank
D. The system logs out the user
Q18. For improving user experience, what simple interface cue did the instructor add to highlight the zip code field?
A. Made it bold and underlined
B. Added a splash of yellow behind the field
C. Added a sound notification
D. Enlarged the entire form
Q19. What is one benefit mentioned of using DLookup and NZ together in Access VBA code?
A. It prevents runtime errors when no matching data is found
B. It automatically updates all related tables
C. It improves VBA code readability
D. It disables user input
Q20. In the context of this lesson, why might you want to allow users to freely type city and state even if a zip code triggers autofill?
A. The autofill is always incorrect
B. Users know more accurate information than the database
C. Some zip codes can refer to multiple cities, and users may need to adjust
D. It increases data duplication intentionally
Answers: 1-A; 2-C; 3-B; 4-C; 5-B; 6-C; 7-B; 8-B; 9-C; 10-C; 11-B; 12-B; 13-B; 14-C; 15-B; 16-B; 17-B; 18-B; 19-A; 20-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 Access Learning Zone focuses on building a zip code lookup system in Microsoft Access. I'm going to explain how you can set up your database so that when a user enters a zip code, the system pulls in the associated city, state, and country from your database. If the information for a zip code does not yet exist, the user will be prompted to enter it, thus expanding your lookup table as new data becomes necessary. This approach helps reduce misspellings and maintains consistency throughout your records.
This topic originated from a discussion in our Facebook group, where a question was raised about whether it's best to create separate tables for areas like city, state, and country, or if other approaches would be more effective for minimizing user input errors and keeping data consistent. The main issue with splitting everything into separate tables and relying solely on ID numbers for things like state is that it can create complications, especially if a state or a record is deleted from a table. Also, using autonumbers as actual data in your tables is not recommended.
My preferred method is to use a zip code lookup table that stores city and state names right alongside each zip code. I do not like tying these to IDs in the main customer table, because certain zip codes can include multiple towns. For example, a zip code like 14075 in New York covers both Hamburg and North Boston, and residents may need to choose one or the other, or even enter a different town name depending on their address. So I like having a default, but still allowing users to make manual changes if they need to. For today's lesson, I will focus on the basic version with a single default city and state for each zip code.
To get started, I recommend downloading my free customer database template from my website. This template includes a basic customer table and form that you can use right away. In this structure, fields like city, state, zip code, and country are all simple text fields, allowing flexibility for users to make changes when necessary. While this approach is not completely normalized according to relational database theory, there are practical reasons for allowing free text in these fields, as real-world addresses and requirements often do not fit neatly into strict normalization rules.
The customer form should let users tab directly into the zip code field first. When a zip code is entered, if city and state are currently blank, the system will attempt to look up the related city, state, and country from a zip code lookup table. If it finds a match, it fills in those fields automatically. If it does not find a match, it prompts the user to enter the missing details, which are then saved to the lookup table for future use.
To achieve this, you will create a new lookup table with fields for ID, zip code, city, state, and country. Populating it with sample data will get you started, but the real value comes as users add more zip codes over time. When modifying the customer form, you want to capture the after update event for the zip code field. In Access, this requires a little bit of VBA code. When the zip code changes, the code will check if city and state need to be filled, perform a lookup in the new table, and act accordingly.
You will use functions such as DLookup to find an existing record in the zip code lookup table. If a match is found, you automatically fill in the city, state, and country fields on your customer form. If there is no match, the code will prompt the user to enter the city, state, and country using simple input boxes.
Once the user enters the information, the values will appear on the form and can also be inserted into the lookup table using an SQL INSERT statement. This ensures that the next time someone enters that same zip code, the information is available automatically without having to ask again. The process involves creating a simple append query, copying the generated SQL code, and incorporating it into your VBA.
Some practical improvements you can implement include converting zip codes to uppercase and removing spaces so you have standardized entries in your lookup table. This is particularly handy when dealing with postal codes from countries like Canada, which may include spaces.
With the basic setup complete, your system can now look up zip codes, prompt for new entries when necessary, and build a growing, accurate reference of cities, states, and countries as users interact with your database. You can also expand on this by making the system smarter about handling cases where one zip code covers multiple towns, although that part is beyond the basic version presented today.
For those who want even more advanced features, I created an extended version of this system. In the extended version, if a zip code is associated with multiple cities or towns, the user is given a choice to pick the correct one or add a new one to the list. This enables much more flexibility and accuracy in cases where zip code areas are shared.
All of the topics discussed here, including handling the after update event, using DLookup, performing string concatenation, handling SQL and append queries, are covered in more detail in separate tutorials on my website. If you want the extended version of this solution, you can find it on my site as well.
For step-by-step instructions, including everything demonstrated in this lesson, visit my website at the link below where you will find the complete video tutorial. Live long and prosper, my friends.Topic List Zip code lookup for city, state, and country
Handling duplicate city and state spellings
Pros and cons of normalization for city and state fields
Creating a zip code lookup table in Access
Populating sample data in the zip lookup table
Editing Access forms for zip code-centric data entry
Using the After Update event in Access forms
Writing VBA code for zip code After Update event
Using DLookup function to find records in a table
Handling NULL values with the NZ function in VBA
Auto-populating city, state, and country from lookup
Prompting users for city, state, and country if not found
Storing new zip code entries in memory variables
Assigning user-entered values to form fields
Building SQL INSERT statements in VBA
Using an append query to add records to a table
Extracting SQL code from Access Query Designer
Running SQL statements with DoCmd.RunSQL
Updating lookup table with new zip code entries
Cleaning and formatting zip code input (spaces and case)
Auto-capitalizing zip code input with UCase
Replacing spaces in zip code using Replace function |
