MS Access - Form based input with query refresh

hey everyone this is just a quick tutorial on how to run a query inside a form so you're going to want to input data into that query and basically output your output for the query on the same form you put the data in so it's sort of like putting data in and getting an output all in the one space and you're going to do this by pressing a button that runs a macro to update the query every time you change the input sort of confusing but it'll make a lot of sense once we get into it so firstly what you want to do is create a blank form or any type of form go to design view and we're going to use combo box for this example and it's basically a drop-down list so this is going to be our input so we're just going to create this um this is using my overeats database for my assignment and our example we're gonna have a drop-down that has every order ID in the database and based on that order ID it's going to output the driver so who drove the food for that order therefore in our drop-down we're going to want all the order IDs so I'm just going to set this up quickly and the label for the combo combo box is gonna be called order ID that's not so important at the moment hit finish and we'll get ourselves a combo box we these are all the numbers here that all the order ID is stored in the database next this is pretty important you want to go into design view and you want to click on the this unbound drop-down here the combo actual combo box and you want to name it something I'm gonna call it CB order ID this is actually quite important so make sure it's something you remember I just hit save and while we're at it we'll save the form as form one so there we go now we're gonna go across this query that I've started to make as you can see it's just selecting the driver ID the sort of the drivers name from from the table where the order ID equals whatever's in the combo box now we need to add in a bit of code that detects what's in the combo box so I think mr. Marsden made a tutorial on how to do this um in the s in the design view rather than SQL view but I prefer this a bit more but either way it's just this bit of code so I put it here to remind myself you want to type in forms explanation like the name and the form so we call it form 1 and then the name of the input box so we call it CB order ID and that's where it's going to look for that piece of data that's that's where it's going to search for the the number that's the order ID so that's good now if we hit save this query is done will close the query will open this form and we'll put this drop down to let's say 5 so when the order ID is 5 we should be able to run the query the driver's first name is Steve so that's working absolutely fine now let's get moving with this we can make this this here into we call a subform or an output form so we're just going to do that using the form wizard all we wanted to show is the first name so that's already detected that for us and we want it data sheet because I think data sheet - it looks the best when you're um when you're outputting it or putting something in a subform so we're just gonna go - eat we'll call this output form 1 just for the example and hit finish so that's going to make this so there we go that's pretty simple not much difference but now if we close this go back to the original form going to design we can drag in that output so output form 1 bang here it is this is going to be our output form and this is going to display the first name so if I am change this event it's not gonna work now if I change the order ID it's gonna actually change the first name here so the next step we want to add a button that resets the query so first what you want to do is go to create and create a macro and a macro is basically you program you know a sequence of steps that occur that the computer will run in order but for this one it's really simple we're going to type in reek weary and that's it you're just setting up a macro that's gonna reek weary on when the micro and the macro is called so you call it worry query we can save this Adams save this is just macro or one that doesn't really matter you could probably say in it say that there's something more meaningful if this if you're actually doing this for real we'll save it as macro at one so we can now close this and we can go to the form go back to design view and we want to add a button and this is going to be like the submit button or the update button we're gonna make a button we're going to go to miscellaneous and we're going to hit run macro and we want it to run a macro one which is the macro we just created text we'll just go update query are you can put in whatever you want in there doesn't matter and this of course you'd probably name it something meaningful if you're actually doing this but this is just a test for me so just command 3 is fine this is the update query and now fingers crossed if all is well we should be able to change this number here in the drop-down so we want order ID 15 and update query the first name and the driver was Harvey and this should work no matter what Gregg Steve Steve again Gregg have only seen Patrick etc.etc it'll you know it works no matter what so yeah it's really it's quite simple really in the end and you can style this up so it looks a lot better instead of a combo box you can use a text box so if you if they want to input a date so between this state and another day it's really it works the exact same way you just need a change to the name of the box that you input in in the query and yeah it's it's pretty simple so anyway thanks for watching and good bye

Channel: Leon Marsden

Views: 16,323

Rating: 4.9080458 out of 5

Keywords: MS, microsoft, access, form, query, refresh, based, database, example, tutorial, help, combo, box, input

Id: 5HvZSFruHgg

Length: 7min 10sec (430 seconds)

Published: Mon May 28 2018