Multiple Addresses 5
By Richard Rost
20 months ago
Storing Multiple Customer Addresses in Access, Part 5
In this Microsoft Access tutorial, I will show you how to effectively manage multiple addresses for a single customer using VBA code, SQL, and various form controls. We'll dive into creating filter boxes, modifying SQL statements in real-time, and dynamically updating data via user input fields to streamline the user interface and increase database functionality. Join me as we tackle these essential skills in Part 5 of our series.
Members
In the extended cut, I will show you how to take the customer address information that's currently stored in the CustomerT (both bill-to and ship-to addresses) and move them over to the AddressT table and make matching entries in the junction table. We'll use VBA recordset loops. Cool stuff!
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!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, store multiple addresses, Microsoft Access, part 5, VBA basics, SQL modify, insert query, wildcard searches, after update event, combo box filter, text box filter, address search, query modification, Access SQL, form design, bind address combo, SQL statement, button for adding address, event handling, dynamic filters, subform controls, Access developer techniques
Transcript
Today is the fifth and final part of my multiple addresses series. Well, I say final. It's the last one I got planned right now. Sometimes months later, I'll come up with a part six when people ask questions. But for now, this is the last one. We're going to do part five, then I'm going to do a little extended cut for the members, and then we're going to be done with it for now. But if you haven't watched parts one through four, go watch those and then come on back.
Now, in part five, we're switching over to developer mode, which means we're going to need a little bit of VBA code to do what we need to do. Everything I showed you so far doesn't require any programming. You can come down here, you can do this, you can pick another address from the list like that and you can start typing it in if you want to — 101, and it'll go right to that one. But what if you got thousands and thousands of records in here and you want to put a filter box up top? Just maybe filter it and show just New York or show just a particular zip code or whatever, all right? It will make this list a lot shorter, make it easier to find addresses. So what do you need to know moving forward for today? Well, if you've never done any VBA programming before, go watch this video. It will teach you everything you need to know in about 20 minutes. We don't need much VBA today, just a little bit, a couple of lines here and there. It's just knowing where to put them, which is what I'm going to show you. We're also going to use a little bit of SQL. We're going to modify a where clause, and we're going to do a little insert query with that. But it's easy stuff. I'll show you how. But go watch this to get a little heads up on how SQL with Access works, if you're not familiar, if this looks like gibberish to you. Make sure you understand wildcard characters and how wildcard searches work. You'd be surprised how many developer students I get that never saw something like this with a little asterisk there. So go watch this. Oh, and by the way, I fixed my little QR code thingy. I found a new place. We're going to use an after update event. We're going to type in a little bit of a filter, and then after that field is updated, we're going to update our combo box, right, to filter the records. So go watch this if you've never used an after update before. And go watch this on refresh and re-query. We're going to use re-query today too. These are all free videos. They're on my website. They're on my YouTube channel. Go watch them if you haven't, and then come on back.
All right. So the first thing we're going to do is put a little filter box up here where the user can type what they want to limit it because one of the problems with a combo box, right, is if you want to limit it to say just the state, okay, if you come down here and type in NY, it doesn't do anything because it has to be at the start of the combo box. So this is nice if you know the street address, or if you start typing in 123, it will bring you right there. But I want to be able to filter based on any part of the address. So we'll put a text box here and then another combo box over here that will filter to show just the filtered records and then we can just add it down below. All right. So let's start off with the text box. I'm going to just borrow a first name, copy, paste. All right. We'll slide it over here and I'll rename this label here. We'll call this the address filter. You can call it address search. You can call it whatever you want. All right. And then we'll slide you over here like that. This doesn't need to be that big. Usually, you put like small bits of text in here. Now double-click on it. We're going to name it. Go to all. We're going to name this the address filter, and we're going to get rid of the control source. Delete. That is now an unbound box. It's not saving its data anywhere. We're just going to use it temporarily. We don't save it in a field under the table, for example. Okay? Now this guy is going to filter another combo box. I don't want to have to recreate it, but let's just grab this one. Click on it once, that will select the whole subform. Click on it a second time, that will select the combo box. Copy it. Now click back up here and paste it. See that? I just basically made a copy of it from the subform up here. Okay, now it doesn't need to be that big. And notice we're getting the little triangle thing here because this field does not exist in the customer table. That's fine. That's fine. We're going to fix that. Move that over here. Okay, now open this guy up. All right, the address combo is fine. We can leave that name because this form doesn't have another address combo. You can have an address combo up here and an address combo in there as long as they're on different forms, including subforms; that's fine. But we're going to also unbind this guy. These are both unbound controls. We're not saving their data anywhere. We're just hanging on to it for a minute. We'll do something with it when we're done. Let's see what we got so far. Save that, let's close it, open it back up again.
Alright, so here I got a little text box. It's not doing anything yet, but I can type in it. It doesn't do anything. But what's going to happen is I'm going to come in here and type in the main and then hit tab. And when I do, it's going to filter this list based on what's in here. That's where our wildcard search comes in. Okay? Now, where does this guy get his data from normally? Well, design view, open her up, put in data. There's the row source right here. All right, let's take a look at this in more detail. Now, you could make another query for this if you wanted to, but we're at the developer level now. So instead of making a second, like an address filter queue, that's how I would teach beginners how to do it; we're going to do it a little more advanced. We're going to just modify this SQL statement and use the same query. Now, since our data is all coming from address queue, I don't need this address queue repeated everywhere. So we're going to get rid of that. We're going to get rid of that. We're going to get rid of the brackets around here. We don't need them because we're not using spaces in our field names like good little boys and girls. And sometimes when I'm working with queries in here, it's easier to do it like this, so you can see everything one line at a time. Yeah. Access is going to fix it in a minute and put it the way it likes it, but this makes it easier to read. Select the address ID and the full address from the address queue, order by full address. A nice, simple, easy-to-read SQL statement. Now all I'm going to do is add a where clause in here. Now, the where has to be in front of the order by, so we'll do that. We'll say where. And what are we going to say? We're going to say where the full address is like whatever's in the address filter and we're going to put an asterisk on both ends. Okay, so we're going to say where full address is like star and address filter, that's the box we just created, and a star. Now, what happens when the address filter is blank or null or empty? You're going to get like star star, which is the same thing as saying show me everything. But if there's something in the address filter, now we get a like star main star. So it's going to be like star, any word that's got main in it, the whole entire address. That could be a zip code, a city, a state, whatever. Okay, see what we got going on? Are you with me? If not, I've got several seminars and lessons on SQL. This stuff, SQL is very powerful. Learn it if you're going to be working with Access on a developer level. Definitely learn this. I learned SQL long after I learned VB, and once I learned SQL, I was like, why didn't I learn this first? So trust me. I'll put a link down below to my SQL seminar. All right, so hit OK, and then we're going to close that and let's just close this and save everything and open it back up again and see what we got. All right, drop this down. All right, the box looks good. Okay, it's showing me everything which it should be, right, like this null, star, star, null, star, and it's exactly what it should be. Now if I put main in here, okay, and then I drop this down, it's still showing me everything. Why? Because we need to put something in the after update event for this text box so that when the user changes this, it re-queries that combo box. Okay? And it's real simple, it's one line of code. Design view. Come in here, double-click. Events. You want after update. This fires after that box is updated. Yeah, there's other events you could use too if you wanted to update every single time they type in a character, like a search as you type. You can do that as well. I got a separate video on that too. All right, and that will, every time they type a character, it will re-query that list. I think that's a little bit too much for this, but if you want to, I got a video on that. I'll put a link down below. I think after update for this is just fine. So we're going to go after update, and your VB window opens up. There it is. And we are going to just simply say in here address combo dot re-query. That's it. That's all you really need. And we're going to add a couple more things to it in just a second. But that's all you need. That's going to say, okay, after the user types in something in this address filter, re-query that combo box. All right. Save it. Come back over here. Shut it down. Open it back up again. Okay, now; notice I've got everything. I'm just going to type in main, m-a-i-n, tab, and now when I open this up, wow, look at that, it's filtered. If I get rid of it, let's put in a 1422, we're looking for maybe that zip code, tab, drop it down, oh there we go, look at that looking for records in here with maybe a street name called Florida or Floramond or whatever. But basically, it'll filter it down to what you're looking for based on what's in your table. Now, I said we could add a couple more embellishments. How about this? Come back into your code. Let's do this. Let's say address combo dot set focus, which normally it would because usually when the user's done typing that, they hit tab. And if your tab order is set up correctly, it's going to move there anyway. But this just makes sure it does. Then re-query it. Then, how about we open up the combo box? Address combo dot drop down. Open her up. Save it. Come back over here. Come over here. Let's type in New York, tab. Oh, look at that. There's all the New Yorkers. Let's put another New York one in here. Let's go to address T. Let's go just 222 Main Street, Buffalo, New York, 1422. Okay, so we have another address. All right, now if I come in here, let's go 142 and hit tab. Boom. one four two and it's out as I do one forty two whatever the so this works in the cool and if you are just have a lot of that went to the next record that's one of my pet peeves too uh... that's just because these two were added last so they're less than a tab order so I can come up here and go uh... form design tab order will take the address f which is the sub form and move that to the end So at least when I'm done here, it'll go into the subform All right, let's go. Okay, if I type in one four two hit tab, and I can click on that and now if I hit tab again, okay, so This all looks good. Now. How do I get this down here? But without coding, you could, if you wanted to, just copy it and paste it because that's going to be in the box. But how about we make an add button? We have a little button here that says add and it just drops it down on the bottom down there. Wouldn't that be cool? And again, that's not hard. It's a little bit of SQL, a little tiny bit of VBA. We can do all kinds of powerful things. So let's make a button, design view, drop this down, let's find the button, put it right over here. Now, the wizard is going to start, we don't want the wizard for this, there is no wizard that does this, so cancel. I'm going to change the caption so it says add and let's make that font a little bit smaller, I like to go 9 point on my little buttons, that's my little helper buttons. Okay, stick it right there. Let's double-click on it, give it a good name. All right, maybe Add Address Button, whatever you want to call it, doesn't matter. OK. And now let's go and put the code in this button. We basically want to take whatever's in here and add it as a record down here with a little bit of code. Really, you'll need like two lines of code. So right click, build event, here we are in the add address button, click. Now before we get to that, it's time for a brief, really, really brief advertisement from our sponsor. And who's our sponsor? Our sponsor is me. If you like learning with me, if you enjoy how I teach and you're learning some stuff and you think this is fun, I got tons of developer lessons on my website. I teach SQL and VBA and all this good stuff, all the things that I cover in my "TechHelp" videos, I got in my full courses too. Well, not all. A lot of it. For example, one of the things we focused on in this video series is many-to-many relationships. I cover that as early as my Access Expert Level 7 class. No programming required. We learn about junction tables and all kinds of stuff. This is just some of the stuff you learn in my expert series. Expert level three, for example, we start covering SQL basics. And I got lots of lessons where I talk about SQL. It's not one of those things you have to learn all at once. As you can see, I like to cover things in bits and pieces. Like I'll cover a little bit here, a little bit there, a little bit going on. So every couple of classes, you learn a little bit more SQL as it fits the situation. That's how I teach. But if you're not interested in the full courses, I do have seminars available on a lot of these topics. For example, my SQL seminars, three-part series, part one's the basics, part two's action queries, part three's the advanced stuff like manipulating table structures. Lots of good stuff in the seminar. Okay, advertising's over. See I told you it would be a short one, a little quickie. All right, five-part series, I got to put a little teeny tiny bit of advertising in there. Okay, so how do I get that value from this combo box down into here as a new record? Now there's a lot of different ways you could do this. You could use do command, go to control, and go to record commands and jump around. You could jump to the subform, jump to a new record, jump to the... I try not to do that. I used to do that a lot when I first started out, when I first started learning Access. There's nothing wrong with that. Do command go to control the record I got several videos on those too but if I can avoid that I tried to because when you're using those commands to jump around different fields on forms, any number of things can accidentally take the focus of the minute it's messed up so i want to do a different method we're gonna actually add a record to the table underneath and then just re-query the combo or re-query the subform. You could do it with either a record set or just one line of SQL code. Let's use SQL. It's easier. So let's come over here. Here's our button. First, let's check and make sure that there's something in that combo box. So if isNull address combo, then we could be nice and we could drop the box down and give them a message. I'm just going to say exit sub. That's up to you. If there's nothing in there, exit out of there. Or give them a beep or whatever. So at this point, we've got something in the combo box. So now we're going to insert it into our junction table. So it's going to be current db.execute. That's how you run an SQL statement. You can also use docommand.runsql if you want to. Remember to turn the warnings off. Insert into CustomerXAddressT. This is an append query. What are the fields you want to insert? Customer ID and address ID. If we look at our table, this is the table we're inserting it into where the data goes for in here. Open it up. I want to put a value in the customer ID and a value in the address ID. Address type ID, unfortunately, we don't have it at this point. The user can always add it after the fact, all right? It's not required. Okay, so we got that. Leave a space at the end, very important. Leave that little space there because we're Okay, values, what's this got to look like? Now inside parentheses, I'm going to put the customer ID, which will get the value off the current form. We're now sitting on the customer form. So it's going to get the customer ID off the current customer form. If you don't have it there, make sure you add it. Sometimes it'll get it, but it's best to have the field there. And then we need a comma. It's going to be a 3, 6,
TOPICS:
Switching to developer mode in Access
Introduction to VBA for Access
Using SQL in Access
Modifying SQL WHERE clauses
Creating and using INSERT queries in SQL
Implementing wildcard searches in SQL queries
Using unbound text boxes and combo boxes in forms
Setting up and using an after update event
Utilizing the requery method in forms
Managing user interface with text and combo boxes
Coding strategies for filtering data in Access forms
Developing user interactivity in forms
Setting up event-driven programming with VBA
Inserting SQL code into VBA
Applying SQL append queries
Data manipulation through SQL and VBA
Quiz
Q1. What type of programming is necessary for implementing the filtering feature discussed in the video?
A. Java programming
B. VBA programming
C. Python programming
D. No programming is required
Q2. For what reason would you use SQL in this Access application?
A. To change the color of the user interface
B. To modify a WHERE clause and perform an insert query
C. To increase the speed of the computer
D. To connect to the internet
Q3. When building the filter for addresses, what is essential to use according to the tutorial to capture any part of the address in the search?
A. Exact phrases only
B. Numerical filters only
C. Wildcard characters in the search term
D. Strict case-sensitive search criteria
Q4. Why do you need to re-query the combo box after updating the address filter text box?
A. To save the new address directly to the database
B. To ensure the changes are permanently stored
C. To update the list of addresses shown based on the filter
D. To clear the previous entries in the database
Q5. What does setting an unbound control mean in the context of Access forms as described in the tutorial?
A. The control is linked to multiple databases.
B. The control is directly saved and secured in the cloud.
C. The control does not save its data to any field in the database.
D. The control automatically binds to all fields in the database.
Q6. What occurs if the address filter text box is left blank or null according to the tutorial?
A. No addresses will be displayed in the combo box.
B. Only addresses containing 'null' will be displayed.
C. The form will automatically close.
D. All addresses will be displayed as the filter defaults to show everything.
Q7. What was modified in the SQL query to allow filtering addresses based on the input from the address filter text box?
A. SELECT statement
B. INSERT INTO clause
C. WHERE clause
D. ORDER BY clause
Q8. What event triggers the VBA code to re-query the address filter combo box?
A. On Click
B. After Update
C. Form Load
D. Before Update
Q9. In order to manipulate the database records directly, which SQL operation does the tutorial suggest using last?
A. SELECT
B. INSERT INTO
C. UPDATE
D. DELETE
Q10. How is the address added from the combo box to the customer record according to the tutorial?
A. Using an automatic wizard
B. Manually by writing it down
C. By using SQL to insert the address into a junction table
D. By clicking a magic button that automates the entire process
Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-D; 7-C; 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 TechHelp tutorial from Access Learning Zone is the fifth and currently final part of my multiple addresses series. I do want to mention that while this wraps up the series for now, it's always possible that I will revisit it with a new part in the future if additional questions come up. If you have not yet watched parts one through four, I highly recommend starting there for the complete background before jumping into today's lesson.
In this fifth segment, we're moving into developer mode. This shift means that we'll be adding a small amount of VBA code to accomplish our next objectives. Up to this point, everything I have demonstrated could be done without any programming. For example, you've seen how you can pick different addresses from a list or start typing an address to jump directly to it. However, with a large data set, scrolling through endless records can become unwieldy. What if you want an easier way to filter addresses based on a city, a zip code, or another part of the address? That is exactly what we'll address today, building a filter box to quickly narrow the list.
If you've never worked with VBA programming in Access before, I suggest checking out my introductory video on VBA basics first. You do not need to know much VBA for this lesson, just a handful of lines and an understanding of where to place them. We will also work with some basic SQL, specifically modifying a WHERE clause for filtering and using an append (insert) query. If you're not sure how SQL works in Access or are unfamiliar with wildcard characters (like the asterisk), my videos on SQL basics will help you tremendously. Wildcard searches are essential for the kind of flexible filtering we're about to build.
We'll also make use of the AfterUpdate event and requerying form controls. If those aren't familiar, I have tutorials on both topics available for you to review first.
Let's get started by adding a filter box at the top of the form. This allows the user to type in any value they want to limit the displayed addresses, making searching far more efficient than just relying on the combo box's ability to jump to the beginning of a string. The combo box by itself is only useful if you know the start of the address. We want something more flexible - the ability to filter on any part of the address, whether it's city, state, or zip code.
The approach here is to add a text box for users to enter filter criteria and a combo box that presents only the filtered results. You don't need to reinvent the wheel for this. You can copy an existing text box (such as one for first name), move it into position, and rename it appropriately, such as "address filter" or "address search." This box should be unbound, as we're only using it to temporarily hold the user's filter value – it's not meant to be stored in your tables.
Similarly, copy the combo box control you use in your subform and paste it up in the main form beside your new text box. Again, keep this combo box unbound. The purpose here is to simply display a filtered list of addresses based on the user's input.
Now, configure the row source of this combo box. Normally it's based on a query of your address data. Instead of creating a brand new query, given that we're in developer territory, we'll directly modify the SQL statement behind the combo box. The basic structure here is to select the Address ID and full address from your address query or table, ordered by full address. Next, you'll append a WHERE clause that uses a wildcard search on the full address field. If the filter box is blank, the query will return everything; once the user enters a value, only addresses containing that value will be shown.
If the SQL syntax is new to you or you'd like to see more advanced work with WHERE clauses and wildcards, I encourage you to review my SQL seminars for a deeper dive.
With the combo box configured, our next step is to make it respond to changes in the filter text box. The key event here is AfterUpdate. Every time the user finishes editing the filter box (typically by hitting tab or clicking away), we want the combo box to requery itself and display the new filtered list. All that's needed here is a single line of VBA code in the AfterUpdate event that tells Access to requery the combo box. It's that simple. You could also add a line to set the focus to the combo box and another to force it to drop down so users immediately see the new set of choices.
Testing this out, you'll see the combo box immediately responds to your filter. Enter a city, a zip code, or any part of the address, and only matching results will appear.
To refine things further, you may want to adjust the tab order so that moving through the form feels natural. For example, after entering your filter and tabbing out, the focus should move directly to the combo box and then down to the subform or next logical field.
Now, let's talk about getting the selected address from the filtered combo box into your subform or into the appropriate table. While you could manually copy and paste, it's much more user friendly to have a button that does this for you. This is where we add a command button, set its caption, size, and name, and write the short bit of VBA code needed to perform an insert into your junction table. This code should first confirm a value has been selected in the combo box, and if so, execute an SQL INSERT INTO statement that puts the current customer and selected address values into the junction table. If more user feedback is needed, You could add message boxes or error checking, but at its base, this is just a couple lines of straightforward code.
There are a number of different ways you could perform this action. Some people like to use various DoCmd methods to move around the form, but I personally prefer going straight to the table with SQL for stability and reliability.
Throughout the lesson, I mention several related topics and strategies that I teach in my full classes, such as working with many-to-many relationships and junction tables, as well as getting more advanced with SQL and VBA. If you're interested in learning more about those topics, I have courses and seminars covering all the material in a progressive, practical manner. You're welcome to check those out for a more comprehensive education.
That's it for this multiple address series for now. You now have the tools needed to filter large lists of addresses easily and efficiently, take advantage of event-driven programming with VBA, and perform updates to your data with clean SQL logic within Access.
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
Switching to developer mode in Access
Introduction to VBA for Access
Using SQL in Access
Modifying SQL WHERE clauses
Creating and using INSERT queries in SQL
Implementing wildcard searches in SQL queries
Using unbound text boxes and combo boxes in forms
Setting up and using an after update event
Utilizing the requery method in forms
Setting tab order for access forms
Creating add buttons to insert records
Writing VBA code for form events
Using SQL to insert new records into tables
Validating user input in combo boxes
Populating junction tables with SQL and VBA
|
