Video instructions and help with filling out and completing Can 8850 Form Relating

Instructions and Help about Can 8850 Form Relating

Welcome to another tutorial brought to you by access learning zone comm in this tutorial I'll show you how to set up a multi field search form so you can look for records in your Access databases this is an intermediate level tutorial you should know-how to build basic tables and forms and queries however this lesson will contain no programming in today's lesson we'll start out with a customer table with a bunch of different fields first name last name city and state we'll build a form with multiple fields on it where we can type in whatever records we want to search by for example show me all the Smith's from Buffalo and we'll also say do not use first name or state as a criteria if those fields are left blank and then we'll make a button to show us the results and a query and of course you can use this query to build a report with in today's tip I've got a customer table with a bunch of customers with some different fields I got first name last name city and state and I want to be able to generate a report showing just the customers that I select based on any of these criteria so I might say show me all the customers from Buffalo or all the customers from Buffalo with the last name of Smith but I want to make a form that's got parameter text boxes for each of these fields and if I leave one of these fields blank I want it to assume that I want all of that value so for example my form will have first name last name city and state but if I leave state blank I wanted to assume all the states so let me first set up a form with my different parameter fields on it I'll go to create and form design I like to build my own forms now this form itself will have no data in it but I want to put for unbound text boxes on it so here's text boxes drop on there and I'll just copy and paste it four times copy paste paste paste all right so I've got four unbound text boxes I'll just set up the labels so I've got first name I've got last name I've got city and I've got state all right let's make these a little bit wider and let's give each of these textboxes a good name because we're going to be referring to these values right right now they just text zero text to text four and so on let's open up the property sheet you can double click on it or you can right click and go to properties the property sheet and find the name control all right now call this first name same as on the table you might see some people call it txt last name but I don't usually use that txt just a matter of personal preference all right city and state alright so there's my four unbound text boxes on my little form I'll save this form I just call it my search form search f-for search form cuz I'll be using it to search for records now close your form and open it back up again and you'll see it's just blank it's just sitting there and yeah we could turn off all these record selectors and navigation buttons and all that stuff we don't need all that but I just want to have it open and sitting here for now now this form will have to be open and working for the query that we're going to build next to work to find its values so let's create a query that we're going to use to read in these values and limit our list of data all right so create query design now I've got two tables in this database because I use it for a different example for another video but we just want customer T our customer table and then I can click on close alright there's our blank query now I'm going to bring in each of these fields individually I don't need the tech ID for now so just bring in first name last name city and state and if I run the query you can see there's all my records now to limit these values based on a value on a form we use this kind of notation right in here we say equal forms the name of our form the search F it pops right up in Access 2010 it's real nice and giving you that search F and then exclamation point in the what field well in this case we want first name alright I love this in older versions of access you had to sometimes go back to your form and see what was the name of it but in 2010 it gives you that nicely alright we'll just do this one field for now alright this says first name has to be equal to forms search F first name alright let's save this I'll say this is my search queue my search query okay and if I run it right now if I run the query you can see I got nothing that's because I said that first name has to be equal to whatever this value is and right now this values blank okay so if I put a Jo in here in first name and then run my query look at that I see Jo I'm seeing all the records where first name is equal to whatever's in this box okay that's the easy part so far I also want to make it so that if this is blank I see all the records in other words the user is not specifying show them all okay so come in here back into our query I will go