Microsoft Access Multi-Field Search Form

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 back to design view and instead of saying equal I want to use the like keyword so get rid of the equal sign I put the like in there like and then inside of quotes put an asterisk that's a wildcard character that says any number of characters I don't care followed by an ampersand all right that says tack this following thing on and at the end of it put another ampersand and another wildcard character that says show me any characters followed by whatever happens to be here followed by any characters and just to demonstrate a notepad what happens is this gets assembled like this access says okay like star Jo star for example so show me all the records that include the word Jo okay if I put a Cate in there it'll look like this okay if that's blank it'll look like this which is the same thing as saying show me all the records alright that's what the like keyword comes in handy so now save my query close the query down go back to your search form I'm going to throw a little button down here just using the wizard to run that query for me now you could use the query to feed a report I'm not going to actually build a report but you can use whatever results come out of this query to feed a report but I'll just open the query here so I'll go to design view find the command button drop a command button down here it's under miscellaneous and then run query next search queue is the only one I've got alright run query put text on there next command 8 is fine so now I've got a button to run that query for me instead of having to open it and close it and open and close it alright save the form close it I'm going to reopen it again now if I run the query now I get all the values okay because it says like whatever that field is star that star inside so now I'll change this to Jo run my query there's just Jo see that I could put a J in here run the query and I'll see all names with just a J alright what else we got for records here how about them oh let's put an O in here and I'll see all the customers who have an om their first name all right now if you want to limit this to more than one field go back to design view take this and then go copy tab paste paste paste and just change your field names right this is going to be last name right this one is going to be city and the last one over here is going to be state alright save the query now I'm going to close the form and reopen it all right if I run the query now I should see all the records if I say city has got to be Buffalo buf run the query there's my two customers from Buffalo if I say it's got to be Buffalo and it's got to be a Smith I'll put SMI in here there's my Smith's from Buffalo so you see this will narrow down your results by whatever criteria you specify and that is how you make a simple search form using unbound text boxes using the like keyword in your queries now there are tons of additional options that you can do here obviously you can specify and vs. or you can use combo boxes so the user can select from a list of values you can actually scrap the whole building and under lying query thing and build the SQL directly and I show all of these concepts in my SQL and my search form seminars that I have available on my website the SQL seminars are available now the search form seminar will be out in early May 2011 and I cover this and tons more techniques and setting up various types of search forms and search buttons in your Access databases now of course remember you can run this query and then take this these query results here and feed this into a report so this doesn't have to be just a query or you can even open up another form that shows you just those records there's lots you can do with access I hope you've enjoyed this tutorial on building a multi field search form for more lessons just like this one visit my website at access learning zone com

Channel: Computer Learning Zone

Views: 579,949

Rating: 4.8636003 out of 5

Keywords: microsoft, access, access 2010, search form, search, multi-field, like, wildcard, query, forms

Id: CTiA_4Me0cI

Length: 10min 30sec (630 seconds)

Published: Wed Apr 27 2011