Microsoft Access Search Form - MS Access Search For Record by TextBox

welcome to another free tip video brought to you by access learning zone comm I am your instructor Richard Ross in today's video I'm going to show you how to make a search form we're going to make a customer search form with a text box and a list box I'm gonna show you how to search customer records in today's tip I'm going to show you how to make a search form so you can search for a customer by typing in for example a few letters of their name and then it will update your customer list to show just those customers with that in their name so here I've got a real basic Access database that I use as kind of a template so I also keep recreating stuff like this it's got a real simple customer table customer T right ID first name last name and so on it's got a very simple customer form alright and this is kind of my template for lessons so I have to keep rebuilding these from scratch if you want to learn how to build something like this and watch my beginner access level one class that is on YouTube and on my website for free and it'll teach you how to build these things ignore the label stuff that's just from a previous lesson where I showed people how to make mailing labels but anyways what I want to be able to do is here's my customer list my customer table all right now yeah there's only three customers in here right now let's pretend there was 3000 and I want to be able to have a forum where I can quickly type in let's say ro and find all of the customers the last name ro now yes you the access developer can simply come in here and use the search tools and do the things that we access developers know and love and it's easy for us however your end user the person that you're building this database for might not have those skills and you might not want them digging around inside your tables or have to teach them how to come in here and do a search based on a field we want to make it nice and easy and simple for them alright so what I'm going to do is first I'm going to create a blank form alright here's my blank form form one and I'm going to put a text box across the top and then a list of customers below it alright so let's come up here I'm going to grab a text box and drop that right here and I'll put in here search that's just my label right and this thing here will call the search box so open up its properties there's the properties I don't want text 0 I want let's call this the search box okay and then below that we're going to put a list box with my list of customers now before I can make my list box I need to make a query that's going to limit the values in the list box it's going to filter them with a condition a criteria that says only show me the customers where the last name let's say you can pick any field that you want or in one of my more advanced classes I show you hi do multiple fields but for today we're just gonna focus on one field last name where the last name has these letters in it all right so we need to make a query first before we can make the list box let's save this form this will be my search F my search form I like to end all my forms and ass alright so let's create a query next create query design I just want my customer table in it close that alright I'm going to bring in the customer ID always good to have the ID that way that's in the list box too and I can do other things with the list box like double click on the customer to open up the customer form but that's that's later all right bring in first name and last name now for last name I need to put in here some criteria all right now if I want to just exactly what's in the last name box I could say equals forms search f you see a little helper box pop up there and then search box which is right there search box if I want it to be exact alright but I don't want it to be exact I want to be able to use wildcards so instead of equals I'm going to say like quote asterisks quote ampersand that says put a star in front of it and then whatever that criteria is and then put a star at the end of it alright I teach this in my classes too if you don't know how to use a like keyword that's a basic query design let's cover my axis beginner level 5 class all right but that basically says like star which means any number of characters and let's say there's just an R in there right like star our star which means as long as there's an R anywhere in the field ok now I can save this I'll call this my search queue for query my search queue okay now this won't work is this form is an open so this form has to be open for this query to work because it's got to have somewhere to get its criteria from all right so let's close that now we have a query that we can build our search form on okay so again let's create and now we want to go to the design and find a list box there's a list box tool drop that there now I want the list box to get the values from that table query that's fine go to queries there's my search queue alright what fields do you want well let's bring them all over next how do you want to sort them I'm going to sort last name and then first name next now you're going to get powned name errors in here because access the wizard cannot run the query because this form technically isn't open there's no data there so the power name errors is accepted just ignore that we're going to hide the customer ID field by going like this watch Boop get rid of it just like that hide the key field all right the reason why access the reason why the wizard didn't offer that option for us like it does with tables is because we're basing this off of the query that's a little quirk all right next what field you want to base this list box off of in case you want to do more stuff with it or refer this value later customer ID is fine and again I cover all of this in my beginner classes and then finish alright so there's my list box I'm just going to delete that label all right so now I've got my search box now save that I'm going to put a splash of color on this form because that white background is is irritating me alright there we go so there's our search form I'm going to save it I'm going to close it I'm going to open it back up again now you can see I'm getting all of the records in there and if I put something in here like our o nothing happens all right because we have to have some way to trigger this list box to wreak weari itself all right when I change this now we can make a button all right a button will work or I could make an event to do it all right I'm going to use an event called the after update event so that whenever this has changed then this will update itself alright first let's open up the properties for this list box that we just created though the name of this list box is lists for I don't like I don't like the default names that get generated when you create objects so we're going to call this my search list all right that's the search box that's the search list it's a list box all right now let's go to the search box go to events we're going to set the after update event for this guy so after update hit the dot dot button pick code builder we could write a macro for it it's literally just one line of code this we're going to use a code builder now I've got all kinds of classes teaching you how all of this work all right but for now we're just going to come in here right inside this private sub search box we're going to type in search list dot read query and that's it it's one line of code see once you learn VBA you can do some pretty cool stuff with just one or two lines of code so save that ctrl s I'm going to come back over here I'm going to shut down the properties I'm going to close this box reopen up the search form I'm going to type in ro and then press tab and look at that my box now limited is required and it limited itself to just the customers with last names ro ro s okay SM there's Smith right you could put this in a button too and make the button make the code run in the button if that's easier if you think your people will understand that better okay pyro right they just type it in and press tab or enter now I do have a class Microsoft Access developer level 8 where I take this concept a lot further where I make it first of all where we've got first name last name and company name and you can search based on any of those three fields I'll show you how I do that in the class so if I type in our I all right notice also it runs in a different kind of event it searches as you type all right so if I just go R it gives me the ours if I go RI without pressing tab or enter immediately it refreshes the box as I'm typing and now you can see the RI is in first name here and it's here at the end of this company name so you can search in any number of fields if you want to it's a little more complicated but it's not that hard to do and I show you how in my class developer eight put the links to all this in the description below the video but this involves a couple of different events a couple of functions like key press and a lot of stuff that I show in that class and I also teach you in developer 9 how you can then double click or click on one of these customers and then have it open up the customer form and there's the customer form I hope you've enjoyed today's tip once again I cover this in a lot more detail in my Microsoft Access developer level 8 class I'll put a link in the description below the video my name once again is Richard Rost and I hope you'll come and see me at my website access learning zone com if you enjoyed today's video I'd appreciate a thumbs up and a comment make sure you subscribe to my channel and visit my channel page also you can come to my website at access learning zone comm and you can watch level 1 my entire three-hour beginner level 1 access class for free it's also here on YouTube and you can get level 2 for just $1 if you want to keep learning you

Channel: Computer Learning Zone

Views: 201,126

Rating: 4.8629284 out of 5

Keywords: microsoft access, search form, search as you type, ms access, event programming, vba, keypress, onchange, after update, afterupdate, like keyword, ms access search for record by textbox, microsoft access search, access wildcard query

Id: HSnHGQeX9so

Length: 10min 12sec (612 seconds)

Published: Sun Aug 05 2018