MS Access: Filter data on a subform in MS Access

hello friends welcome to another video on Microsoft Access this video is about filtering data on a subform in this application we have a main form on the main form we have a subform that displays data we have to combo boxes for filtering and may have a command button that clears the filters I will take you through setting up the combo boxes for filtering and we will write the code that filters the data [Music] let's get started now the data that's displayed on the subform resides in this table TBL org let me open that for you there you go now the subform displays all the data that's there in the table but what we want to be able to do is to filter some information based on two criteria one is the country and the other is language so let's set up our combo boxes for filtering now we want only those values that exist in the table so for countries we want only those countries in the country column similarly for languages we want only those that exist in the table so the first combo box that we going to set up is for country as you can see I have named it CMB country now in the data tab we're going to leave the row source type as it is table slash query we have just one column in this particular case so bound column remains one will change limit to list to yes because we want the user to be able to select only what's in the list and we don't want him to be typing in his own values now for the row source we are going to type in a query and what we want are the distinct values from the country column now if you click on the button with the ellipses it brings up the query the query designer and you can design your query visually if you wish but I'm just going to type it in and this is a select distinct country from table org let's give it an order by clause so we can order it alphabetically let's test the query to see if it works and it does so let's save the form I'll open it and form view and let's have a look and we have the list of countries in our drop-down now let's do the same for languages so all options remain the same only the query changes and this time we're selecting distinct languages select distinct language from table org I'm going to toss in the order by clause to order it alphabetically so you perform let's have a look at it on the form now that we have both our combo boxes set up let's write the code we write a sub procedure that performs the filtering and we will use the unchanged event of each of the combo boxes to call that procedure but before we do that let me explain how filtering actually works now I'm going to close the main form let me open the sub form and when I go to the properties sheet and the data tab you have a property here called filter now if I type in for example country which is one of the columns of the table and I set that equal to let's say Brazil so let me set filter on load - yes if I go to the forum view you can see the country has been filtered and you can see only Brazil now you can have multiple criteria for the filter you can set country equal to China for example language equal to Mandarin and as you can see the forum has filtered the data according to the criteria specified now what we need to do is achieve this programmatically so in our visual basic editor let's type in the sub procedure we will call it filter form now in this procedure we will check for the values in each of the combo boxes and build a filter first let's check for blank values let's check the country combo box first if that's blank and the language combo box let's check that if both are blank you set the filter on the subform the subform is this one here so form underscore frm or data dot filter is equal to blank I'm going to toss in a comment no filters now you might be wondering what NZ does it checks if the first parameter pass to it is not and if it is it returns the second parameter it's a very handy function to check for nulls I believe a link in the description to the official documentation so let's proceed so we will check now if a country has been selected but not a language so we're going to toss in an else--if clause this time we're just going to check if CMB country the value of CNB country is not blank and language is blank so only a country has been chosen but not a language let me put a comment their country filter only the filter will be country equals and the value of seeing the country now you need to use quotation marks to pass that value from CMB country because it's a string alright so the next thing we do is check if the language has been selected but not a country so let's toss in another el elsif clause and this time we're checking in CMB country is is blank and language is not blank let me add a comment language filter only and the filter this time is the value from CNB lang now if none of the conditions above have been met it means that there are values in both the combo boxes so let's just toss in an else clause so the filter this time will be the value from CNB country the country equals seeing the country dot value let's add the end clause and then language equals and the value from CNB blank and we have one last thing to do which is turn on the filter filter on equals true and with that we've completed us a procedure we just now need to call the sub procedure from the unchanged events of our combo boxes all right we have a button here called clear filters and when the user clicks this button we want to be able to clear the combo boxes and remove the filtering from the subform so in the click event of the button we clear both call combo boxes so CMD country equals reset that a blank CNB Lang equals blank and we call the sub procedure okay so let's save the form open it in form view and have a look [Music] [Music] [Music] and the filtering works as expected fantastic and that's it for this video thank you very much for watching please share and comment [Music] you

Channel: Evil Programmer

Views: 3,012

Rating: 5 out of 5

Keywords: MS Access, Subform, VBA, database, microsoft access, filter data, office 365, MS office, Visual Basic, Visual Basic for Applications, combo boxes

Id: fXxxZ4FbY4g

Length: 11min 20sec (680 seconds)

Published: Sun May 05 2019