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