Getting a Value from a Form for Query Criteria in MS Access

welcome to a free mini tutorial brought to you by access learning zone.com i am your instructor Richard Ross you can find this and tons more free tips and tricks plus other tutorials on my website at access learning zone comm slash tips in this tutorial I'm going to teach you how to get query criteria from a forum in other words pick a customer on a forum and then run a query showing all the records related to that customer I'm going to show you how to enter criteria manually in a query I'm going to teach you about the forms form name field name notation which is how we get information off of a form then I'm going to teach you how to use a list box to select a customer then press a button to run a query showing all the orders just for that customer now I consider this to be an intermediate level tutorial so if you have never created forms or list boxes or use the command button wizard I strongly recommend you take one of my basic tutorials first I've built a sample database file that has two basic tables in it I've got a customer table which simply has customer ID and a company name very simple I have an order table order T that has order ID which is the auto number the customer ID which links back to the customer table so this is customer one customer two and so on and if you don't know how to set up a basic relationship between tables that's a different tutorial I cover that in my Access 201 tutorial on my website I also have a couple of other informational fields here about the order such as order date and the order total you can have as many fields in here as you want I've set up a simple form for my customer called customer F that simply has those two fields from the customer table in here now what I'd like to be able to do is once this customer form is open I'd like to be able to generate queries or perhaps reports based on just this customer and I want to get the information from the customer form now if you go into design view and you look in the toolbox under the command button wizard there is an option where you can open up another form and show specific records to display in other words open up another form for example an orders form and show only orders for this customer but that only works with forms you can't do it with queries or reports so we're gonna learn how to do this manually now in order to do this I need to know what field I'm referencing and what form name I'm referencing this is the customer F form and I want to get the customer ID field so I can limit my query based on that information so remember that or write it down somewhere alright now go back to form view so your form is open and you've got a record sitting here otherwise this won't work switch over to your database window and go to queries and then new go to design view and hit OK now I want to make a query that shows a list of all of the orders just for the currently open customer so I'm going to bring in the order table and you can also bring in the customer table that'll show us customer information pertaining to this order hit close my two tables are now on my query and you can see they're joined because access found a customer ID field in each that's fine let's say I want to see the order ID the customer ID the order total and the company name just those four fields now if I run the query now I'll see all the records but let's say I only want to see the records for customer one well in design view under criteria I can simply put a 1 here and then if I run my query again I see only customer 1 but I don't want to have to type that one in or have to have my user type it in so I'd like to get this information from the customer form that's currently open so here's what I'm going to change my criteria to forms exclamation point customer f/x Clah may ssin point customer ID that says go to the forms collection on the customer f and get me the result in the field customer ID now if you hit tab or enter you'll notice that access puts a little square brackets around everything that's fine but that's how we tell the query to get this criteria from that form but if I run my query now you'll see only customer one if I go back to design view and then switch over here to my customer form and change to a different customer let's say customer two when I come back over here now and run my query it automatically updates and shows us customer two so that's how we can get information off of the form and you can use this notation in pretty much any location you want whether it's a criteria on a query or in another form field or even in a report but now that we have this query let's save it and knowing how to do that now I can build other queries or I can build other forms or reports that pull off of orders for current customer queue to get their information if I have customer 3 open and I run this query or any report based on this query it'll show me just information for the currently open customer here's another example I built another simple form called customer list form customer list F that basically has just a list box on it the form has no records in it so the form is unbound which I covered that in another tutorial but you can simply come in here and pick a list of customers now I did this with the wizard using the list box wizard and a button down here also from the command button wizard that you click on and it runs the query alright there are no orders for that customer let's pick a different one and that opens the query and shows just the records from the currently selected customer ABC Corp click and there they are this is a new query I created orders for customer list queue but if you look and design it usually the same principles right here's the same table same fields down here under customer ID I'm simply getting the records from forms customer list F customer list box that's the name of the list box so when I click on the button down here it opens it up and of course you could use the command button to make it open up a report or another form that's based on this query that's all for this free mini tutorial I hope you've enjoyed it you can find more on my website at access learning zone.com slash tips that's my tips and tricks section you'll also find more free tutorials like this one in addition to some sample database files the database that I created in this tutorial can be found on this webpage if you need help with Microsoft Access feel free to submit your question to me on the web page I hope you've enjoyed this tutorial and of course you can find more at access learning zone.com if you do decide to purchase any of my videos use order code youtube 279 to receive a 25% discount on any videos you purchase

Channel: Computer Learning Zone

Views: 149,314

Rating: 4.728302 out of 5

Keywords: computer, microsoft, access, form, query, criteria, forms!formname!field, parameter, value

Id: QUcjL7Cy5n0

Length: 7min 28sec (448 seconds)

Published: Mon Jan 07 2008