Creating a Form-Based Parameter Query - Dynamic Queries

in this tutorial we're going to be looking at creating a dynamic query where the query will be taking information from a form with an unbound combo box that when we click on the button will call the query where the information be taken from the combo box where the data be required so in this case here we're going to try to create a class list so from courses we can actually select the class like opt click on generate class list the crew will run look at the form to find out what the criteria for the query is in this case here it's IBT and then run the results of that and if we wanted to from the same form we could then select something else like English click on generate class list and it brings up the English class list for us so let's get underway and make a dynamic query now the first thing we need to do is create the form so we're going to go up to create and we're going to create the form not using the wizard this time but we're actually going to create the form in design view so we're given a blank palette what we would like to do is place on here a combo box so the combo box is located here and we're going to just draw a combo box onto the stage it's now going to run a wizard for us and what we want to do is get the information about it a bollec on next what table because we're doing a class enrollment what we want to do is actually find the class code and also its name so it's going to come from table of courses and what I would like to see is the course ID and also the title of the course and click on next we can sort these by their title or their course ID I'm going to sort by course ID so that way I'll have the year a and nine year ten courses so the sequential and when we come into this section here you can see that we have the not in alphabetical order and we're going to see the title of the course we don't actually see at the moment what the primary keys are but if we uncheck here we will actually see the primary keys when we use a lookup wizard and when we use these sort of combo boxes what is actually stored in the combo box is the primary key this first field even though we could just be viewing the title this is what would be querying on and that's important to remember so I want to see that and then when I select an item I just want to see the code not the actual name of the course and click on finish so you can now see that we have course ID here I can come in edit that and change that to this course and I now have an unbound list so let's say this with the command s and I'm going to call it frm class list when I run this I now have a combo box that gives me all my classes you notice you've got 7 9 10 11 so they're in chronological order and I can make a selection from the combo box next we need to create a query so we need to go up and go create and once again we're going to make the query in design view now the first thing we need to do is other tables I'm going to hold down my shift select them all and add all the tables and click on close this will also give me if I close the properties window will also give me the relationships as well now what I want to see is I want to find the first name and the last names of a student doing a particular course so course ID now in our query we need to make sure that the criteria is always first so I always move my criteria up because we're searching for a course ID now this course ID is coming from this combination box here so this combination box actually has a name so we're going to just on a view and in group design and right mouse click it and open the properties when we go to all you can see it's actually called combo zero and what I'm going to give it is a naming convention of CD for combo box and I'm going to call it class list so it actually has a meaningful name rather than this combo 0 so this combination box now actually has a proper name or an instance name so in my query I want to actually search for the information that the person has entered into this combination box here so to do that I'm going to cheat a little bit and use the builder so right mouse click and go to build and I'm going to go into my database into forms because that's where the combo box is all forms and you can see here form class list and I'm looking for that CB class list and when I double click this you know survey is already highly and a writer's string at the top I'm just going to highlight that and copy that and click on OK and it pastes that information into the form for us so at the moment if I put this in a runtime view and select opt when I go to my query and run my query with a view you see that only IPT classes come up if I close my query now and just save that as q ry class list if I change this now to English and run the query only the English classes come up so what happens in this case is the query is looking back at the selected content of this combination box and uses that as the criteria to run the query and shows us the results now if we don't want to see the course ID I can actually just turn that off and save that when I view it now you can see the names so by using the tick's you can actually show information or not show the information so it's up to you now what I'd like to do is create a bit of an automated system so I want to create a button on my form that will call the query so I'm just going to close my query and then on my form go back in the design view I'm going to go into the design I'm going to select button and I'm going to draw a button and once again it will create the wizard for us but we want to go under these miscellaneous and run a query and click on next what query query classlist and rather than use a picture I'm going to trade two texts to generate class lists and click on next and click on finish so now I have a button on my form that when I click this will open up the query the query will then get the information on the unbound box for its criteria and then show the results of the query so let's save this close the forum open the forum app select IP T then click generate class lists and there's my class list if I then close the query and then open English and generate class list that's my class list for English so what we've done there is created dynamic query that uses a combination box that looks up the contents of a table so it is unbound as this is not stored in the table but just looks up informations already stored and then we created a button that cause the query that looks at our combination box and use the information selected in combination box to drive the criteria of our query

Channel: Leon Marsden

Views: 69,551

Rating: 4.861042 out of 5

Keywords: MS, Access, Microsoft, Database, queries, query, dynamic, criteria, form, based, parameter, object, combo box, unbound

Id: mnWidUABYdQ

Length: 7min 24sec (444 seconds)

Published: Thu Apr 20 2017