How to Launch and Filter Reports Using a Form in MS Access

i'm your host sean mckenzie thanks for joining me once again on my channel on data analytics and data engineering in this episode we're going to return to our microsoft access playlist and we're going to do a combination of things that's very common uh when people are starting to make applications with microsoft access and that is we're going to create a microsoft access report and then we're going to launch the report from a form and we're going to filter the output of the report using some fields on the form so without further ado let's get to our filtering using form and reports in microsoft access okay so i started a new access file here just created a blank database and then i imported some climate change data which comes from the world bank which you can download from there if you uh so wish to and as you can see it's got all different kinds of measures for different measures and different countries and sometimes there there are entries and sometimes there are no entries in in each year however it gives us a a nice example that we can use country code or probably country name and series in order to filter our report if we make a report based off of this this data so what i'm going to do here first is i'll create a new form and so we'll use the create ribbon and in this case i'm just going to use the the access form design and that'll that'll give us something easy to work with so just a blank form design that we can throw some fields onto and in this case what i'm going to do is i'm going to grab a two combo boxes you can use text boxes you can use check boxes you can use all different kinds of controls now click cancel here if the wizard pops up because we're just going to do this directly on the objects we're not going to use the wizard for this particular case so uh the combo box comes with a label and so we'll fill in our label there we'll just call it series and that sort of gives us a nice label and then we can name our combo box because we don't want it to be called combo zero so we'll call it cbo series and then you can see under the data tab of the properties you can see the row source there so click on the ellipsis and it's going to give you you know options of tables in your database and we only have one and so what we're going to do is we're going to select the the country name our series name and country name for this for this example so we'll start with with series name and and so we'll double click on that and then we're going to go up and go into the sql view and we're going to add the word distinct after our select statement there or after the select and that's going to give us just a list of all the countries without all the rows and so it's kind of a nice way of getting some selections and we'll sort that in ascending order so that it makes sense to people and then when we click to exit that the sql query builder there it asks us to save and we'll say yes and so what that gives us is uh it's going to give us a nice combo box for us to use so we'll save our form and and then we'll call it filter underscore form and that'll give us a nice text box that we can use and so we can test that out by going to the form view and then you can click on your on your drop down or combo box and as you can see it's kind of a short box it's too short for the entries in there so we're going to extend that over and do a little design work here just to make it look better so the first thing we can do is just simply extend the box over and that's going to give us a nice nicer sort of reading box for reading however you can see that the list itself is still still too short and sometimes you can't extend the box and so one way to alleviate that is to go into the list with which is usually set to auto and you can see down below that the width of the box is eight point six so eight centimeters so we'll just make our list with 12 centimeters and so it can hang over like edges of of you know forms and things like that and it can extend out further so as you can see on the right side that gives us lots of space in case we have some really long entries there and so this gives us a nice list it's sorted kind of makes sense to everyone and and we can sort of move on from there and from there what we can do is we can go to our design view once again and we can select our series uh combo box and we'll copy and paste that using control c control v and that's gonna copy that box and we'll change our label just like we did before and then we'll change the name again just like we did on the first one we'll call this one cbo country so means it's or it gives a lot more meaning to the name of the box and just like we did before we'll go into the uh into the sql and if it was a different table we would right click we would remove table and then we would choose another one so in this case it's the same table but i'll i'll re-add it just so that you can see that and then for this one what we're going to do is we'll double click on the country country name and and then we can go like we did before to the sql and you can see it left the distinct in there because we copied and pasted the object so we don't have to add the distinct in and then we can sort that ascending close it just like we did before we'll click yes and then we've got our country drop down list in a combo which looks really good and now if i go and i test this out i can open it open it in form view and you can see there's our series and also our country drop down with some nice selections in there now we can choose any country that we want and so from there what we can do is we can go back to design view and we're going to add a button to our form and again the wizard might pop up but you can click cancel on that and we'll just type in open report and if we put a ampersand in front of that you'll see it gives an underscore which means when the user is using the report they can go alt r and it will click the button as a keyboard shortcut and we'll rename that as well cmd open report and now you can see we've got our button and our two filter criteria there so we can save our form and close it and then move on so our next step is to create a simple report so we're going to use the report wizard for this and we only have one table in this case and so we're going to just choose a few fields so we'll choose the series name and the and the country name and and then we'll say that we want to get for this particular report we always look for 2008 9 and 10 and and so we'll choose those columns and we're not going to add any grouping levels so we'll just click next and then we will sort it by country and then series and then tabular is just fine for this report and portrait is fine as well so we'll call we'll call our report we'll give it a name called climate report we'll say okay and then there we go there's our climate report looks a little rough still got some left and right justification issues uh but for the most part you can see the report looks okay there so as you can see i can close our object list and uh i'll just uh scroll over here so you can see you can move forward and backward through the pages and uh i'll right click on the tab there and go to design view and we'll clean up some of this stuff here but first we will go and we'll take a look at our uh coding that we're going to do in order to uh in order to facilitate a filter on here that we can manage from the form so we're going to go to the on open event of our event tab there and we're going to click on the ellipsis which will pop open the choose builder pop-up and we'll double-click on code builder and that's going to give us our report open event which is going to execute a little bit of code right as the report is opening so that it will prepare the report for for being viewed and this is a good place to add a filter and you can do all kinds of other stuff too you can run updates on tables and all kinds of stuff in here which will happen just before the report opens and so when the report does open and pull data it'll be the data that's been adjusted by or perhaps during the report open event and that's what we want so the first thing there is we're going to dim frms form which is going to give us a form object to work with and then we'll set that form equal to forms exclamation filter form which which will now give us a nice handy little pointer to the the form that we created with our two dropdowns on it and we'll also set up a filter so we'll dim a string as a str filter as string and we'll set that to an empty string just to start and then we can sort of get to work so when the user opens the report if both of the drop downs are empty then we don't want to do anything further and so we'll just exit our our subroutine here or the report open event subroutine so we'll say and in this case i'm going to use the method where i'm going to concatenate the two in case one is null so i'll say if an empty string and the series the length of that is zero and the length of uh concatenation of an empty string and this and the country is equal to zero uh then we'll just exit out of our out of our sub and uh um that's gonna allow us to capture that event where they don't actually choose anything but on our way out we'll set our our filter uh equal to an empty string and we'll say that our filter on um property is false and then we'll exit our sub and that'll sort of capture that and it'll set the filter of the form back to nothing in case something had been set there before and i'll go up and put a note about this here i'll just say if both are empty then show everything and uh that will put no filters onto the report so then we can go ahead and we can say well we know at least one of them has a has an entry in it so we'll say if if the length of our empty string and the cbo series is greater than zero then we know something's in there you can to get that one you can also say if is null equals false you can use the is null instead of the len function you can say str filter equals series name [Music] is equal to what's in our our cbo series so there's a couple ways of doing this and i'm going to show another way at the end of this at the end of this episode i'll show you a way to use a parameterized version of this as well but in this case we'll use we'll just plop that into our our string filter and i will say is equal to our our series and then end quote on there and that's going to give us our our filter string and then we can move on to our country string and see if we need to add the two together so for country we'll do kind of a similar thing we're going to say if the length of the the combo country combo selection is greater than zero then we can we can add that to our filter string so the first thing we're going to do is check if our if we got anything in our filter string already from the series and so if the the filter string has anything in it then obviously we need to put an and onto our filter and so we'll just go ahead and we can say that our filter is equal to the filter and then we'll put and on there and that'll put a nice and onto our string [Music] in front of the in front of our our country filter so that it'll say the series is equal to this and the country is equal to that and if there's nothing in the series then it'll just say the country is equal to whatever the selection was so we'll say our str filter equals str filter and country name is equal to and we can use we can use a concatenated string here for production purposes stay tuned to the end of this closer to the end of this video because i'll show you how to use the parameterized version of this and and so there we have it i'll take out the underscore in our cbo because that's not in our actual form and then from there everything sort of looks good uh we just need to add our filter onto our report so we'll just say add filter we can start that with our comment here and then we'll just say me dot filter equals our str filter that we've uh put together and we'll say mu.filter on is equal to true and that will cause the report to put a nice filter on there depending on the user selections uh in in our form and so there we have it we've added our code to our open so that it will filter the report for us and i'll do a little cleanup i'll left justify that country name and we'll we'll make a few changes here to the size of these just because the the data comes out kind of funny that series name is quite large and so we can go over and these number columns are not very uh they're not that big so we can select all of those guys and we can just uh change the size so that they're all the same and then i'll just move those to the right and uh i'll move that one to the right and move the 2008 one to the right and then i'll extend the box of the uh of the series name just so that um so that it looks a little bit nicer when we open it so if i save that and we go ahead and take a look at what that one looks like that's probably going to look quite a bit different so i'll close it and now we can go and open it from our form so what if i make a selection in our in our form here um you know i could choose anything anything i want but i might just choose a single country and what if i put in a country name and then click ok and you'll see that nothing happens because we haven't actually uh set up our our button yet so in order to do that we'll go to our design view of our form and uh we can just click on our button and we'll go to the on click event and we'll click the ellipsis and the code builder double click just like we did before and that's going to give us our open report event so cmd open report for the command button and uh this is where we can say i use do command dot open report and we'll give our our report name in there and we're also gonna put in ac view preview otherwise it'll send it to the printer and that's really all we need to do there so we're pretty much done with the button and you can see how easy that was so now if i uh click our open report button i've chosen sweden if i open the report you can see there we go it's all sweden and it's all these different measures that were collected for sweden and that's kind of how that filter works or i could choose you know sweden and i could choose one of those measures like say co2 uh or something like co2 output or something like that so if i leave the country as sweden and i choose let's choose our co2 emission total and you can see that it gives one line for sweden and co2 emission total and so that's kind of how that works it's uh makes it nice and easy to filter your report at uh runtime so you can choose one country and you can extend you can see all of all of the different series and the measures for that particular country and so you can do this in your your own report and you can use all different kinds of controls you can leave a free text form instead of a drop down box you can put dates in and you can say between this date and that date and all kinds of things and so it makes it nice nice and easy to use so there's our open report and again there's canada and it looks great if i wanted to just look up uh not just a country but just a series in all different countries then i could choose say something like cereal yields uh per hectare and it would give me all of the countries and their serial yield per hectare and so that's nice and easy to use as well so it works for all the different combinations of those boxes whether you leave them empty or if you choose one or the other of the criteria and it's a nice way of filtering the report okay and now for those of you who might have your work going into a production setting of any kind of serious nature you're going to want to use a parameterized filter instead of just a concatenated filter and that's going to make it a bit safer although these are just filters they're not actual sql strings however you can parameterize these which make them a bit more safe and and by in order to do that you need to add your expression here so it's forms exclamation filter underscore form exclamation cbo series to get the value from there and for this one it will be the same except for it'll say cbo country for the selection and and that's a another way an alternate way of doing it which is just as easy and can save you a bit of trouble and now if i go and i test it out here i can close that and open my filter form and i'll choose say canada again with our parameterized version it also works the same way and so that's an alternate way of putting your parameters in there and you'll see i can also use the series if i choose serial yield just as we did before click open report you can see there's our serial yield for all our countries and that is how you filter create a report and filter it and launch it using a form in microsoft access i hope you enjoyed today's episode on filtering reports using form values in microsoft access if you like what you saw today make sure to give the video a thumbs up and subscribe to the channel if you haven't not subscribed yet and click the bell when you see the belt so that you'll be notified of any new content i put up on the channel if you have any comments or questions about what you saw today please make sure to leave those in the comment section below and i'll be sure to respond to as many as i can and have a great day have a safe day and i'll catch you next time

Channel: Sean MacKenzie Data Engineering

Views: 5,377

Rating: 5 out of 5

Keywords: filter report access, filter report in ms access, access filter reports using combo box, access open report from form, ms access filter report vba, access command button, vba report on open, vba combobox, access combo box parameter, vba open report, vba filter report, sean mackenzie, data analytics, data engineering, data tranformation

Id: bdmCmYYETac

Length: 24min 31sec (1471 seconds)

Published: Fri Jan 08 2021