17. (Advanced Programming In Access 2013) Filtering Sub-Forms Using VBA

hello everyone and welcome to programming in Access 2013 the Advanced Course my name is Steve Bishop and in today's video we're going to be talking about filtering sub forms using VBA code now in our last video we did a filtering of our sub form based upon the combo box the selection of the combo box but we were using the special built-in functionality of access of the linked master field and child fields what we're going to do in this video is actually not utilize this functionality of access but rather have everything handled by VBA code instead so essentially what we're going to need to do is we're going to need to tie in the query that fills in the data of our sub form which is this record source and we're going to need to filter it we're going to need to change the data or change the record source to filter based upon what's been selected in the employee combo box and whenever you're talking about filtering your queries or filtering the data within query you're talking about altering the where Clause or altering the having Clause depending upon whether or not it's an aggregate query so what we're going to do is we're going to take a copy of the current record source here so I'm just going to start and copy the whole thing here and the time that we are the moment that we need to change the data that's in this subform is when there's been a change of the employee that's been selected so we're going to go to the combo box here and we're going to go to the events tab and the events that we're going to be triggering the change of the data on is going to be this after update okay because after update is after the the combo box has been updated and the data that gets sent out the data that gets that this combo box you know basically has as the value returned is this employee ID okay it's the bound column is one and the first column in the combo box row source is employees dot ID so this combo box is going to return the ID of the employee that's selected and we're going to use that value to filter our query inside of this subform okay so here's what we're going to do we're going to start off here in the combo box go to our event tab and go to the after update click on the ellipsis and go to the code builder and as usual I do type in the option explicit up there at the top now since we're changing the query which is text we need to create a variable that is going to contain that text so we're going to do dim SQL as string and let's go ahead and set the value of it equal to the value that we copied either the current query that is set for that subform and I'm just going to kind of spruce this up a little bit so I'm going to add some some extra add a quotation and drop it down a line okay and one thing that you might want to consider doing when you're kind of making this go down to the next line is try to find the keywords within the query so we've got our select statement from there to there now at our from statement we're going to start from there we're going to start a new line from there so drop that one down so there's our from and we've got an inner join there and an inner join there and then here's an on statements we'll start with a new line with the on statement drop that down a line and then so there's another on statement there's our where Clause so we'll drop that to its own line and we've got an order by statement so we'll add to that and that's it so our query now is a little more legible here's our Select statement on two lines here's our from statement with the on portion here the on keywords for that from statement on those two lines then we have our where statement there and our order by there it just makes things a little easier to be in a little more organized and easier to understand where the thing is okay so now what we need to do is the string we actually need to set to the wrote the record source property of this form okay so this record source property of this into this form that's set inside of this subform object is what we need to set that sequel query for and since we're starting at the form active orders right are our code here these starting from the main form the parent form this one here so we need to start from there and drill down from there through our subform down to our regular form so we're going to start with me and the name of the subform object which was sub active orders and then we can just reference the form itself we don't need to give it the name of the form we could just say form and that's going to going to direct us to the actual form that's being set in the subform and then we're going to set the record source equal to the sequel query okay now it's not enough that we just set that property equal to the query we actually need to tell the form to go out and look for the results again okay and the way you do that is with a simple function here its sub active orders again we need to draw all the way down to the form itself that's holding this new property or holding the record source property and the method is called pre query and that will actually take this sequel query and re query it and fill in our our form so let's go ahead and save that for now and I'll just show you what it looks like at this point let's go ahead and take out the link of master fields and child fields to get rid of that and as a matter of fact what I'm going to do is I'm going to go and clear out the record source that's in here right now let's just go ahead and delete everything that's in here doot-doot-doot okay and when we open up the form we're going to just kind of get this blank nothing right now right there's no active orders because we have no employees selected and this data is not going to be filling in anything right now because we we cleared that record source out we won't be setting the query until we do something to change this employee combo box so let's go ahead and hit the drop-down let's select Andrew and there we go we get all of the records okay you'll notice this is not filtering right now so I can select anybody here and we're still going to get all of those same records over and over again we haven't actually changed our where clause yet to filter based upon the employee yet so let's go ahead and go back in here and now what we're going to do is we're going to change in the event let's go to after update and now we need to change our where clause to include that one thing where the employee ID is being filtered by whatever has been selected by the combo box so we're going to go ahead and add an and statement to our query so we're saying where this happens where status ID equals 3 or not status eat ID equals 3 and we want to take the employee ID which is the foreign key of the orders table okay this orders ID employee ID I'm sorry let me say that again this orders table with employee ID is the foreign key of the orders table which references which employee is associated with that order and we're selecting from the combo box the employee and it's returning back the employee ID that we're wanting to filter the data by so we're going to go ahead and say orders we're going to do employee ID equals and since the employee ID that's getting returned back by this combo box is a numerical value we don't need to use any special quotation marks or apostrophes but it is something that we're setting we're getting the value of that combo box outside of the string okay because you can't just put the the combo box name inside of the sequel query you actually have to use the VBA code you know have to rely upon the objects of VBA in order to pull that value back and it's not included within the string so we have to do an ampersand here to concatenate and we're going to do me dot CBO employee and that's going to return back the value of the employee that was selected from the combo box now one thing here just to take note of is that if I were to just leave it like this that's going to add that employee ID number that's selected and you'll notice that there's a missing space here so I need to add a space at the end of my statement here just so that there's a space between this orders employee ID equals and then whatever the value is of the employee ID and then there's a space after that before it goes to the order by statement okay so that's why you need this little space there I could have put a space right here and that would have done the same thing so you can put it in either place it doesn't really matter okay let's go ahead and save that and just compile it to make sure it's all right and it looks good okay so now let's go ahead and go back here and again we've got really nothing here for active orders until I select somebody let's go with Jan Jan doesn't have any or active orders associated with her if I select Andrew there are his two active orders and for Nancy we've got several active orders okay now some of you may be asking well what about when I have no employee in there and we've got a problem well that's what I'm going to show you in the next video is how to kind of manage some of the interactions that are some of the things that you need to consider about how this you know the things that your users can do that could potentially break this process so let's go ahead and show you that in the next video

Channel: Programming Made EZ

Views: 56,933

Rating: 4.9395771 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, advanced, software, microsoft access, table, query, form, code, coding, development, visual basic for applications, computer programming, microsoft SQL server, SQL server, programming language, subform, sub-form, filtering

Id: M1J1rHXR4JY

Length: 10min 51sec (651 seconds)

Published: Sat Jun 27 2015