|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Access ZIP Code Template In this template 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. LessonsDatabase FilesFree VersionWant to learn more about this template and how it's built? The first video above is the FREE TechHelp version of the template and shows how to work with a single city/state for a ZIP code. Click on the download link to get a copy of the FREE template created in the video. Extended VersionIn the Extended Version of this tutorial, if the ZIP code exists but there is only ONE city in the database for that ZIP code, it will return it and populate your customer form. If the ZIP code exists but there are multiple cities, a form will appear allowing the user to select which city to use.
You can purchase your copy of this tutorial by clicking on the button below. You get the full 27 minute extended video tutorial plus an editable template:
LicenseFor internal use only. This template does NOT come with a royalty-free license. You may only customize the template for your business needs and for use within your organization at one location ONLY. You may not resell or distribute any form of this template to others without express written permission. Contact me for additional information on obtaining a license to distribute if you plan on including this template in a product you are reselling. Additional licenses are required if you plan on using this template with more than ten (10) employees or in multiple locations within your organization. Not a Finished ProductPlease keep in mind that most of my template databases are not designed to be finished products that are ready to go in a working environment. My templates are meant as starting points for you to customize for yourself so you don't have to reinvent the wheel. This requires that you have a basic understanding of Microsoft Access development. To work on most of my databases you should have completed my Access Beginner series and the first couple levels of my Expert series at a minimum. Most of my databases require knowledge of SQL and VBA as well. Keep in mind that most of my templates are not like off-the-shelf software. They're starting points for developers to be able to modify and use as their own. It really is going to be in your benefit to watch all of the prerequisite videos so you understand the database and how it works moving forward. This is not like QuickBooks where you just install it and use it. My templates are mostly designed for people who already have an understanding of how Microsoft Access works. Customize For Your NeedsIf you would like to discuss customizing this template for your needs, and integrating it into your current setup, please see my consulting page for details. While I no longer accept custom jobs that are specific to a single user, I may include your features in a future version of this template if they have mass appeal. If you are looking for custom enhancements made to this template just for you, visit my Developer Network. Technical SupportPlease note that technical support is NOT guaranteed for any of my courses, seminars, or templates. If you require help with modifying this template, you may post a question in the Forums, however an answer to your question is not guaranteed. If this template comes with an accompanying Seminar, then you should purchase that Seminar to see how the database was constructed. If not, then you should have taken the suggested courses. Most of my templates are designed on a Developer level and you should have a thorough understanding of SQL and VBA before attempting to modify them. If you have a problem with one of my databases, I will only support the unmodified database exactly as it's downloaded from my site. If you have modified it in any way, it may not be something I can help you with. You can post in the Forums, and I may be able to help you, but if the issue doesn't exist in my unmodified database, it's not something I can support. Questions?Got sales or customer service questions about this template? Feel free to contact me. If you have technical "how do I" questions about this template, you discover a bug, or want to suggest a new feature, then please post your comments below. Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #templates, zip code lookup
IntroIn this video, I will show you how to set up a ZIP code lookup feature in Microsoft Access, allowing users to enter a ZIP code and automatically fill in the city, state, and country fields if the information exists in a lookup table. We will discuss how to structure your tables to control spelling and duplicates, create the necessary lookup table, add VBA code to perform lookups, handle new entries when a ZIP code is not found, and keep your data consistent by cleaning up ZIP code formatting.TranscriptWelcome 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. 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 will 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 the area of a city table and a state table and a country table - probably meant 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. 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 have every zip code in the country. 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 auto numbers as a piece of data in the table. And then, of course, there are 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, so 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. Okay, 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. 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 these 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 is 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. 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 table. Go to design view. 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 splash 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 or color behind it, maybe like a little bit of yellow, just so it reminds them that something's going to happen. Okay, now we need to build a lookup table next. So let's save this form, close it, and create a table in design view. 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 Canada and stuff. Save this as my zip lookup T. Primary key defined? Yes, okay. You can call this zip lookup ID, whatever you want to call it. All right, we're not really going to use it. Let's put some sample data in the lookup table. All right, I'm going to put my current one in there, so 33966, city is Fort Myers, state is 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. If you're going to have city in there, then we'll trigger the update. If not, then we won't. All right, 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 that's the default. It's a pain to have to answer that every time you're doing something. So pick code builder. That'll 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. Actually, I'm 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. 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 make sure we got the zip code. So I'm just going to MessageBox the zip code, just like that: MsgBox Zip Save that, flip back over to Access. Just on the bottom down by the taskbar, close the customer form and reopen it. I'm going to just change this to 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. All right, 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. Okay, 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. 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 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, Visual Basic beginnings, the message box is 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. All right, 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 about typing 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. 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. That's good because no ID can be zero because autonumbers always start at one and go up. You can also use ID with text strings too and return like a blank string or any other value that you want. That's what the second value is for, 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. 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 'Zip code exists, look up data Else 'Zip code does not exist, ask for it End If Well, the first part is relatively easy. We can say right here: City = DLookup("city", "ziplookupT", "ID=" & ID) This is inside of a text string so Access doesn't do that little thing where it automatically capitalizes stuff for you, and I like to make my code look clean. So if I accidentally put an o in there like that, I want to fix that. Even though it'll work because Access is not case sensitive, it still looks nice like this. Here we don't need the extra quotes in here because ID is a numeric value. Now I can just copy and paste this 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. 33966, boom, Fort Myers, Florida popped in there. All right, if these fields were 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, just lock the text boxes, set the locked property. But I like to leave these editable for the thing that I mentioned earlier. Now, what to do if we don't have that value in the table? Well, we have to ask for it and add it. So let's get three more memory variables: Dim sCity As String Dim sState As String Dim 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") Let's just make sure this works so far. Let's come down here, put something that doesn't exist. Enter city - you put some stuff in it. Enter state pops up next, you just hit OK or Cancel, and then enter country, Cancel. So that's working so far. We just pop up those input boxes. I cover InputBox in my classes too. All right, now we've got these values in memory variables, stored up here in these string values. Now, what we're going to do is add them to the form itself. So let's just put them in the form, then we'll add them to the table. So 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, what, eight years old - I'm not going to say how old I am but it's getting close to 40 years here - people say I've been programming, pushing the big 50 here in a few years. Anyways, let's go back over here, make sure this works. All right, let's put something in here that's not in the table. Enter city - I'm going to just slide the window up here so you can see it better. All right. Let's say city is Somewhere, 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 trickier 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 out of these variables. So a little bit of SQL here, people: Dim SQL as String We're going to put an SQL statement in here now. I'm going to use an SQL insert statement. Here's 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 an 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. We're going to make this an append query. What table are we appending on to? 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. 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. 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, and then Country: just some y's. Now, we also have to put the zip code in, 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 SQL is an INSERT. Copy, come back over to VBA, left it open in the background down here. Right here, I'm going to say: SQL = "INSERT INTO ziplookupT (city, state, country, zip) " & _ "SELECT '" & sCity & "', '" & sState & "', '" & sCountry & "', '" & zip & "'" Now let's take a peek at my SQL statement just to make sure it's correct: MsgBox SQL We'll see what we've got, we'll see what this turned into. We can close that query, we don't need to save it. Let's put something in here, zero. All right, enter city, we'll just put Buffalo, New York, 14222. Okay. There's my SQL statement that we generated, so: 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, "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. All right, zip code not found, Hamburg, New York, country is blank, enter. All right, zip code added to lookup table. Let's go see. ZipLookupT - there it is, beautiful. Let's do another one. Let's do 33993, enter. Zip code not found, that's Cape Coral, Florida, country is 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 4B. Now, see, here's the thing: like 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 1B3, you can actually filter out the spaces if you want to keep your zip code lookup tables clean. All right, but let's put that in there. All right, 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, it 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 you can say: 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 someone types it in with the space, it should still correctly find it. 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's 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? See, and you could theoretically do 140 75, and it'll fix it. Okay, that's how we're 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, 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, all right, if it doesn't exist you can see that we're prompted immediately to put in something, let's call this My Town, Florida. All right, 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 is at 88888, you're prompted for which one is it? 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. And 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, 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? Well, 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 actually 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 one, three hours long, there's a link. Did I mention it was three hours long? And if you like that, level two, 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. QuizQ1. What is the primary goal of implementing zip code lookups in an Access database as described in the video?A. To ensure users can only select cities and states from rigid dropdown lists B. To prevent users from accessing certain zip codes C. To automatically populate city, state, and country fields and reduce spelling errors D. To store only the IDs of cities, states, and countries for reporting purposes Q2. Why does Richard recommend leaving city, state, zip code, and country as freely editable text fields in the customer table? A. To strictly enforce normalized data B. Because sometimes a zip code covers multiple towns and users may need to edit the values C. To prevent users from updating customer address information D. To avoid using lookup tables altogether Q3. What is the purpose of the After Update event on the zip code field in the customer form? A. To immediately save the record in the database B. To reset all form fields when a zip code is entered C. To check if the zip code exists in the lookup table and populate city, state, and country if found D. To lock the city and state fields after entering a zip code Q4. If the zip code entered does not exist in the lookup table, what does the program do? A. It rejects the entry and returns an error B. It prompts the user to enter city, state, and country, then adds them to both the form and lookup table C. It automatically assigns a default value for city and state D. It deletes the customer record Q5. What VBA function is used to look up values from another table based on a zip code? A. DFirst B. DMax C. DLookup D. DCount Q6. What does the Nz function achieve when used with DLookup in the zip code lookup process? A. Converts text values to numbers B. Ensures that if DLookup returns Null, a zero or default value is returned instead C. Zips multiple values into one D. Normalizes all data in the table Q7. How does the system handle situations where users enter zip codes with spaces or lowercase characters? A. It accepts them as is without any changes B. It deletes the extra spaces but does not change case C. It standardizes entries by removing spaces and capitalizing zip codes before lookup or insertion D. It disables further input from the user Q8. Why is perfect normalization of address data not always ideal in customer databases? A. Because customers may move, and past orders should retain the original address B. Because normalization always leads to slower database performance C. Because using IDs is mandatory for all fields D. Because Access does not support normalization Q9. Which statement best describes the process for adding a new zip code entry to the lookup table in VBA? A. A delete query is executed first B. A SELECT query is used to retrieve data only C. An INSERT INTO SQL statement is constructed and executed with DoCmd.RunSQL D. Data is manually added through the Access interface only Q10. What additional feature does the extended version of the database (mentioned near the end of the video) provide? A. Ability to reject all new zip codes B. Support for choosing from multiple city/state combinations for the same zip code C. Automatic state and country selection based only on zip code patterns D. Automatic data export to Excel Q11. According to the video, why is it sometimes necessary to allow users to manually edit city and state fields, even if a lookup was performed? A. Some zip codes cover multiple towns, and the default result may not always be the correct one for every user B. Users may prefer to leave these fields blank for privacy reasons C. It is required by Access for all lookup fields D. Manual edits automatically update the lookup table as well Q12. What is the benefit of using freely entered text for city and state rather than strictly enforcing lookup selection? A. It guarantees 100 percent normalization B. It allows flexibility for unique situations, like multiple towns within a zip code C. It makes reporting more difficult D. It prevents users from entering new locations Q13. When inputting Canadian postal codes, how does the system ensure consistency in the lookup table? A. By rejecting all non-US postal codes B. By converting lowercase letters to uppercase and removing spaces C. By requiring all entries to use lowercase letters only D. By not storing Canadian postal codes at all Q14. What does the MsgBox command typically do in the context of the VBA code shown in the lesson? A. Store data to the table B. Display a message or value to the user for debugging or feedback C. Clear all form fields D. Rename a table Q15. Which tool or function is suggested if you are not familiar with writing complex SQL statements like append queries? A. Use macros only B. Let Access build the append query in query design view and copy the resulting SQL C. Only manually type SQL statements in Notepad D. Avoid using append queries altogether Answers: 1-C; 2-B; 3-C; 4-B; 5-C; 6-B; 7-C; 8-A; 9-C; 10-B; 11-A; 12-B; 13-B; 14-B; 15-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on zip code lookups in Microsoft Access. In this lesson, I guide you through the process of setting up your database so that when you enter a zip code, the system can automatically populate the related city, state, and country fields if that information already exists in your database. If it does not, the program will prompt users to input the missing data, making your database much neater by ensuring the correct spelling and preventing duplicate entries. Users always have the option to manually change the city and state if necessary, so there is flexibility as well.The topic for today's lesson originally came up in our Access Learning Zone discussion group on Facebook. The question was whether to set up separate tables for areas like cities, states, and countries and then use keys to keep user input to a minimum, thereby reducing misspellings and duplicates. This is a common issue, especially when users spell locations differently but use the same zip code, such as various misspellings of Los Angeles. Several members contributed to the discussion. There are external zip code databases available that you can download and import into your Access database to automate this lookup process. This works well for US addresses, but if you serve international customers, you may need to consider how to handle data from other countries. Another point raised concerned using IDs for referencing states or other territories, but this can lead to issues, such as when a record is deleted. I personally prefer having a zip code table with matching city and state names, but I avoid storing just the IDs in my customer table. Some zip codes cover multiple towns, so you want users to be able to edit the entries if needed. For instance, the zip code 14075 in western New York covers Hamburg, North Boston, and several other townships. The same zip code could have various city names, depending on where exactly the customer lives. The simplest solution is to assign a default, such as Hamburg, but allow users to enter another town if needed. To streamline the setup, you can begin by downloading the free customer database template from my website. This template already has the basic structures in place, including a customer table and form. The customer table should have fields for city, state, zip code, and country, all set as text fields. This approach allows users to correct or personalize the information at any time. While it might not be perfectly normalized database design to allow free text entry for location data, sometimes a little flexibility is helpful. There are practical reasons not to enforce strict normalization, such as when you want to record the address used for an order at the time it was placed, regardless of any future address changes. Next, I walk you through rearranging the customer form so that the zip code field comes first. When a user enters a zip code, if the city and state fields are blank, the program will attempt to fill them in automatically using a lookup table. If not found, the user is prompted to provide the missing details. To support this, a lookup table is created with fields for zip, city, state, and country. Populate it with a few sample entries as needed. Then, on the customer form, set up an After Update event for the zip code field. The After Update event in Access triggers programming code when the field is changed. To check whether the zip code exists in your lookup table, use the DLookup function. If there is a match, pull in the corresponding city, state, and country and fill them in on the form. If not, prompt the user to enter the missing information using InputBox commands. These responses are then filled into the form and, with a bit of SQL, added to the lookup table for future use. If the user enters a zip code not found in your lookup table, the program will prompt for city, state, and country, then add this new combination to the lookup table using an SQL INSERT statement. This way, every time a new zip code is encountered, the table expands, reducing future data entry effort and supporting cleaner, more consistent data. You should also consider handling different international zip code formats. For example, Canadian codes may include a space in the middle. You can clean up formatting by removing spaces and converting text to uppercase automatically to standardize the entries in your lookup table. The main procedure described here covers the basic version, where each zip code is assumed to correspond to a single city, state, and country. However, the extended version available on my website covers more advanced cases where a single zip code may serve multiple towns. In that setup, when a zip code with multiple possible towns is entered, the user can select the correct one or even add a new one. This approach provides even more flexibility while still maintaining data integrity and ease of use for the majority of cases where zip codes only map to one city. You will find several additional topics related to the lesson on my site, including in-depth guides to functions such as DLookup, string concatenation, the After Update event, SQL basics, and how to use append queries. You can also find more templates and tutorials for Access database development. For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends. Topic ListOverview of zip code lookup automation in AccessSetting up a customer table with zip, city, state, country fields Advantages and limitations of normalizing address fields Rearranging the customer form to prioritize zip code entry Adding color indicators for user prompts on the form Creating a zip lookup table for city, state, country data Populating the zip lookup table with sample data Adding an After Update event on the zip code field Using VBA to handle the After Update event in the form Demonstrating lookups with DLookup for zip code data Handling DLookup null results with Nz function Automatically filling city, state, and country based on zip code Promoting user entry when zip code is not found Collecting city, state, and country from user input Assigning user input to customer record fields Building and running an SQL INSERT statement in VBA Appending new zip data to the lookup table Using Replace() to remove spaces from zip code entries Using UCase() to capitalize zip code input Testing with US and Canadian zip/postal codes Allowing users to edit or override auto-filled address fields |
||||||||||||||||||||||
|
| |||
| Keywords: access templates zip code lookup PermaLink Microsoft Access ZIP Code Template |
