Review the Customer List Form and Create a VBA Subroutine

Bruce Myron
A free video tutorial from Bruce Myron
Productivity Specialist
4.6 instructor rating • 18 courses • 102,818 students

Learn more from the full course

Microsoft Access VBA, Design and Advanced Methods Workshop 5

Learn new secrets to creating awesome Microsoft Access databases and VBA coding not covered in any of my other courses!

02:09:24 of on-demand video • Updated April 2020

  • Perform several advanced Microsoft Access techniques that will enable them to create more robust database systems
English [Auto] OK let's get workshop 5 started with this section on ranges for combo boxes. I'm doing this because I've gotten several questions on this over the past couple of months. And if I double click on combo filter you should have downloaded this from the link in the previous lecture. I'll just double click on it here. This is a copy of a form and if I double click on customer list it's a copy of a form from the VBA for non-programmers course where we had multiple filters. And so the question is did people understand how we can do one filter for instance if I just pull this down and select a state it does a filtering of all the records by that state and then it also interacts. So that notice the cities are now just Alaska cities and I can select that and I could clear these individually or I could do a CLEAR ALL. So the question is how would we use that same type of programming and do a range. And I've got a date added field over here on the right. So the way you do arrange what makes it simple is don't try and do it all at once have two separate pulldowns combo boxes one for the low end of the range and one for the high end of the range so I've copied this form. These are dead. They don't do anything yet. The Clear button here in the combo boxes are not set up so that will be our objective in this section is to set these up and you'll find by doing it this way it's actually more flexible and it's a lot more easy to do. So let's get started by looking at the code behind these existing filters. I'm going to click on the design view. Oh actually let's make this form larger. I'm not in tabbed view. I mean overlapping windows view here. And the this is pretty small so let's make it bigger. All good to design on the customer list and I'm going to grab the titlebar appear and move it up and then I'm going to go to the bottom and get the double arrow and drag it down. And when I click save the form will remember that. So if I go back to form view the forms now taller and just a lot easier to work with. So I'll go back to design and let me click on let's say that state filter that we used a minute ago and I'll click on the property because I mean overlapping windows I can move this form around and let's see we're going to go to the event tab This is called State filt short for state filter and notice we've got three separate teams here. We've got Got Focus lost focus and then after update if you recall this forum was used to locate records and then we could edit or add customers so you couldn't do any editing on this form. So if I click on the got focus and then I click on the builder button one of the things it does is when you click on the filter you have to be able to make a change. So we turn on edits when we got focus or arrive at this filter. Notice right below there since Access always puts these in alphabetically these subroutines. Here's the last focus right below here. And you notice that all it does is when we leave the field it turns allow and it's off again. The only other thing that's done on got focus is it test to see if there is a filter string and that's the if any filters already set. And it actually sets the row source for the combo box on the fly. If there aren't any filters it just says Select Distinct and that's unique values select the unique values of the state from customer. If we didn't have this distinct word here we'd get all the states for all the customers with all the repetitions. So by putting in distinct we get unique values that also sorts automatically. The only difference is if there is a filter the row source just has this where statement where and then it hooks in the filter string and then it ends the queue with a semi-colon and then the other thing it does is it uses this dropdown command so that when we click on the filter it immediately drops down so we can see that again if I do an 11 activate the form click on the state. So it's turned everything off and I've got my unique list. And here's the dropdown. Now I'll do an all 11 again. What about the after update if I scroll up. Here's the after update it's just one line build filt string and that's on the bottom. This is a this is not tied to a field it's a generic subroutine that all of the filters use and all it does is it sets filter string to nothing. Here's two double quotes. And then it just goes through each of the filters one by one starts with name filt and if there is if it's not blank it's not a double quote then the filter string equals last name and then whatever is in there. The filter it has to test if there is already a filter if the already is it'll add Citi Field to it. Otherwise it makes the filter just city equals city filled. Then the same thing for the city state filled on the bottom and then down here on this section at the very end. It just test if there is a filter string if it's still blank it turns off the filter. If it's not blank it turns it on. This little section on the bottom with the there are some global variables name string city and state string. And those are just recorded so that if we leave the form and go to a customer form when we come back it'll reset those filters where we we were when we left. So that's the overview when I want to do is to press Allt F 11 and start some programming. So all 11 I'll get back to design and let's click on this first field this first combo box for the beginning date added and let's click on. We'll just start with lost focus that's the most simple one. I'll click on lost focus in the properties box. Click on the builder button code builder and OK. And actually you know what I did I forgot to name it. Notice it's got the default name combo. 96. I'm just going to delete that. Delete those rows there and I'm going to do in step 11 back and let me name both of these. Let's call this if I scroll to the top of the all. Should we call this beginning date. And noticed this label name here you might not have this this is because I am on Office 365 and there has been a recent monthly update that has added this label name property here. Before that name was immediately followed by control source. So don't be thrown off by that. If yours doesn't look like then I'm going to click on the next filter and go ahead and name that and date built that way the subroutines will both be named something more meaningful. So I'll go I clicked on a big date filled beginning date. Click on the event again. I'll get lost focus. Click on the builder button code builder in OK and we're back now. I'm just going to go. Let's see. Here's the last focus right here on the Citi Field. I just pressed. I clicked Next to it and I pressed home a couple of times to get to the beginning. The left edge and then I did a shift downer to highlight. I'll do a Control-C and then I'll just use control up arrows to go up up up. There we go and I'm going to do a shift down arrow right here because I know if I do that when I do a control V it's just going to paste without putting in or shoving down the blank row. So that's our first one lost focus. What's good in the second one. All 11 back will go to got focus and don't click on the builder button and code builder and once again I'm just going to look for somebody else here city field copy that and I'll use control of Barrow to get up here. Shift down or go and to control V and this is almost the same but we have to change the filter names. This is beginning date filter here and here. The builds and we also need to change the field name. And incidentally one difference from the VBA course. I went ahead and took the space out of the date added field. I'd put it in there just to demonstrate how that makes things more difficult later but I went ahead and this is one of those instances you have to put square brackets around this otherwise so I went ahead and removed the space. So it's the date added field that we're looking at. Last thing to do is change this to the beginning. They will drop down. So let's stop there. And this lecture is getting a little long so I'm going to stop there and then in the next lecture will test out what we've done and add the got and lost focus to the other filter. So I'll see you then.