How To Create A Keyword Search in Access 2013 🎓

hello everyone and welcome to how to in Access 2013 my name is Steve Bishop and in today's video we're gonna be talking about how to create a keyword search for your forms so in this particular example I'm gonna go ahead and grab the Northwind database the Northwind database template and create a new database based off of it and then I'm gonna create a form and a subform and in that subform it's gonna be a continuous form with all of our customers in the Northwind database and then on the main form it's going to have a keyword search that filters our subform based upon what the user types in the keyword text box it's going to filter it based upon the last name that they type in so let's go ahead and hop out here and go to our Access 2013 instance and I'm just going to go up here and type in Northwind to get our you know to do a search for our templates and you'll see the Northwind database just gonna double click on that and it's gonna go ahead and create a new database for me and there we go okay let's go ahead and Nabal the content close our login dialog and I'm going to change this to be object type just so that we can have I just prefer this particular view in the navigation pane alright so in our customers table we can see that we've got several different columns here but for the demonstration I'm really only going to be concerned about these first for the ID the company name last name and first name okay so what we're gonna do is we're gonna create a continuous form that's going to be our subform that is filled in with these four pieces of information so let's go to the create tab and go to the form design and since it's a continuous form it doesn't need to be too long it's gonna you know get longer as it goes let's change the format from single form to continuous form and now because it's going to be continuous form I like to add a header and footer to the continuous form but actually we don't even really need the footer so I'm just gonna scroll this up here you know make it not there anymore I'm just gonna and drop it so okay so in our continuous form we're going to have those four columns of information so we need to drop in a couple of or drop in four text boxes here I'm gonna get rid of the label for this one this is going to be our ID field and I'm gonna change it so that its size is only eight in font and let's auto size the text box doesn't need to be too wide because our ID field is really just numbers it's only two digits wide right now so it doesn't need to be very big but I also want to add a label here we're gonna call it ID and put that little header at just above the text box so that people when this continuous form gets filled out they'll know that this first set of text boxes is the ID text box let's change it to black and size nine bold and let's again autosize that all right so let's make the next one and this one is going to be our company name so it needs to be fairly wide and let's change this company so what I'm going to do is I'm going to go ahead and pause the video here and just fill out make the other four text boxes and headers here and just kind of make everything nice and size properly so I'll see you in just a moment okay so we've got our text boxes and we've got our headers I went ahead and renamed these two so you can see txt company txt last name etc so we've got the properly named set for our text boxes here now what we need to do is go ahead and set the record source for our form so I'm going to go to the data tab record source and click on the ellipsis here and I'm gonna again we're filling it in with a customer's table so let's go ahead and select the customers table and we only need these first four fields so let's go ahead and drag and drop them down into our design here and I would like to go ahead and order the results based upon the last name in alphabetical order so I'm going to change the last not name sir order to ascending and let's go ahead and save that and close it now we need to go ahead and set the bindings of these text boxes to the appropriate control sources so again just going to click on the data tab for the text box change the control source to the appropriate field the company is going to be company last name is last name and first name is first name okay so let's go ahead and view this just make sure yep we're getting the data that we want in the order that we want we can see it's alphabetically ordered but I do want to go ahead and get rid of the you know these record selectors and the navigation buttons down there so let's go and go back into the design view and get rid of that let's go to the format record selector has changed to no navigation buttons to no and okay that looks pretty good headers look pretty good everything looks just exactly how I want it so let's go ahead and save this form and since this is going to be the subform this is going to be the form inside of our main form I'm going to go ahead and name this with a prefix of SF RM for subform and it's gonna be the customer list and let's go and close that so now we need to go ahead and create our main form so let's go to create tab form design and this one does need to be fairly large because it's going to be our main form it's nice and wide there and big okay in our main form we're going to need to give a place for our subform to show up and you do that by going to the subform sub report object here and let's click on that and just kind of create a space here for where our subform is going to be I don't need to fill that out let's go ahead and get rid of the label and I'm gonna name this object from child zero I'm gonna change it to sub customer list okay and now what we need to do we need to set the source object property here equal to that subform that we just got done creating so let's click on the drop down here scroll down until we see that subform customer list that we just created and there we go so now we've got our subform our continuous subform shows up inside of our main form okay great so now I need to go ahead and create a place for our keywords you know for where the user is going to type in the keywords and go ahead and do the search so let's click on the text box here and let's create a little spot here for the keywords let's give it a label here called keywords already and let's make this I like black makes a little bit more visible make this nice and wide so there's plenty of room for somebody to type in their keywords and I also want to add a button here that the user will click on in order to initiate the search so let's go ahead and add that there okay I'm gonna name this button instead of command four we're gonna call it BTN search and I also need to change it from saying command four to search and then our text box here let's change it from text to two txt keywords and there we go okay so this is the actual input where the user is gonna type in the keywords and then click on the search button but some of you may prefer the functionality of being able to type in a keyword and then hit the enter key right you type in what you want say bishop and then hit enter and you want that to do the search just as much as you want them to be able to click on the search button well you can do that you can set that by going to this search button and changing the default property in the other tab to yes and when you say that this is the default button on the form that means when the user hits Enter key while they're on this form it's going to act as if you just clicked on that search button so it's gonna trigger the on click event when the user hits the enter key on their keyboard so essentially they're gonna be able to type in this up hit enter fish up I should be able to type my own name shouldn't I so they type that in and then hit Enter key and that would act as if they just went over here to the search button and pressed on click okay so let's go ahead and now well let me explain exactly what it is we're gonna do here so right now our subform here this form that has all of our results of all of our customers right now it's being filled in based upon this record source query that we built what we want to do then is filter this these results based upon what the user types up here in the keywords text box and then what you do that is you essentially replace this record source with a new record source that includes whatever we've got here in the keywords text box so let's go ahead and take what's currently there in the record source let's go ahead and copy all of this so let's go and highlight all of it and copy this and we're gonna take that and when the user clicks on the search button that's when it's going to replace the the query right that's filling in the record source when the user clicks on the search button it's going to replace that record source property okay so we're gonna go to the search button go to the on click event do our code builder here and let's do option explicit alright so what we want to do first is we need to build the query that's going to replace this record source and we just copied the original one so let's do a dim SQL as strengths so let's create a string variable called sequel and we're gonna fill it with what's currently there right now okay and I'm going to format this just a little bit to make it easier to read okay so we've got a select statement that's giving the ID company last name and first name from the customers table and ordering it based upon the last name in ascending order so we want to essentially filter this query with the keywords that the user types in here and we want to filter specifically by the last name right we're filtering the last name comparing it to what the user types in the keywords text box and whenever you're talking about filtering a query you can use either the where clause or the having clause depending upon whether or not it's an aggregate query this is not an aggregate query this is a pretty straightforward query where we're getting all the results there's no you know there's no functions on here like min max last first group buy anything like that so we're gonna use the where clause and the where clause needs to go before the order by clause so we're going to stick it right here and our where clause is going to be comparing the last name to what the user types in the keyword text box and we're going to do where last name we could say equals and remember since this is going to be a comparison of a string we need to wrap it up in up in apostrophes so we've got this apostrophe here to indicate that it's text and it's me dot txt keywords which is the keywords that the user types up in that text box and okay oops that's not it we want to use there we go and we do need to add just some formatting here there we go so we're gonna do a where clause lastname is equal to whatever the user types in the keywords section now there's one drawback of doing it this particular way and that is what if the user is not looking for an exact match of what they type in the keywords but rather something that is similar to like it's really close maybe I only the first three letters of the person's last name and I don't know the full last name well then this equals operator here as part of your where Clause is not going to work because it's not going to be exactly equal they want the results that are alike what they've typed in the key word section and because of that instead of using the equals operator we want to use the like operator and when you use the like operator what you have inside of the the apostrophes here you can use wildcards okay and so we're gonna throw in a couple of wildcards both before and after the key word that the user types in and that should now basically fill in a like statement it creates a like statement for whatever the user types in here for the keywords and then asterisk before an asterisk after so that we're using wildcards to match wildcards with whatever they've typed in that are like what the user the last name is okay so hopefully you guys follow along with that I hope that makes sense we're wanting to do a like statement instead of an equal statement all right so now that we've got our sequel query built we need to go ahead and apply it to the record source for our subform okay this record source here so we need to replace it and you need to replace it from this module here which is the form right now it's called form 1 but we need to start from here from this module and drill down to this sub forms record source now what you do that is you start off with me which just points to the form that you're currently inside of right which is this main form here where the keywords text box is so starting there drill down and go to sub customer list now this the sub customer list is the name that we gave the object that fills in the subform okay it's not the name of the subform itself remember we named the the subform is called SF RM okay but we're actually specifying the name of the subform instead you specify the name of the object that contains the subform and that was called a sub customer list that was this thing that we clicked on here to create a new subform right okay so oops you go back up here okay so we wanted to get the name of our source of our subform object called sub customer list and inside of it again we still don't need to reference the name of the form because on our subform object is a property called form and that actually references regardless of what the name of the subform is the the property form references that subform okay so we're saying form and then the property of the form that we want to access is the record source we want to set the record source equal to the sequel query that we just built up ahead up above here okay so we're creating our sequel query that's going to replace the record source of our subform and then here we're actually setting the record source equal to what that query is but that's not enough but there's one more step we need to make when the form gets when the form first opens up and that includes the subform when the subform first opens up it runs whatever is in this record source okay it doesn't run what we've got in the search button okay and once it fills in the results it stays there until you tell it to check again for what the results are of the row so of the record source so we need to add one other command after we've changed the record source property we need to tell it to go ahead and wreak wery okay and that will now cause this subform to wreak weari itself based upon this new record source that we've given it let's go ahead and save this form this is the main form we're gonna call it frm customers and that should be good let me go ahead and do a debug compile just to make sure I didn't mess up anything there and let's go ahead and view okay again we're doing based upon last name everything looks good so far we've got everything is still ordered by last name let's go ahead and use our keywords textbox here and I'm gonna filter I'm gonna look for the results where a X E n is the key word here so ax en for a last name click on search and there we go there's our one record of X in Thomas and let's see if we can get I believe one was called Anderson spelled with an E but instead of clicking on the search button I'm gonna go ahead and hit the enter key on my keyboard and there's Anderson Elizabeth perfect now what about if we want to get all of our results back well we can just go ahead and delete everything in our keywords section click on the search button again and there we go there's all of the records again and that is because when there's nothing in the keywords textbox it's essentially blank it's nothing it's null so this turns into essentially this and we're last name like asterisk asterisk you know two wildcards it's actually gonna pull up anything right it's gonna give us any results at all where there's any last name whatsoever okay so that's perfectly acceptable it's gonna replace our blank with nothing right our keywords is blank so there's nothing in there and so we're going to get all of the results that we're looking for that's a certain amount of functionality I think everybody really likes being able to just delete what's in the key word section and click on that search button again and it refills it in with all of our records all right so that's pretty cool now what about if you want it to not only filter by it yeah you know do a search by last name but you also want it to search first name too because what if I want to type either Elizabeth or I want to type in Anderson okay well here's what you can do let's go back into the design view here go to the search on click event and we're gonna change this where' clause to have like on just the last name let's now go ahead and add an or statement the first name could also be like asterisk need txt keywords okay so now we're going to filter our customers table where the last name is like whatever they've typed in the keywords or the first name is like whatever they've typed in the keywords okay so either one if if whatever they type in the keyword section matches either the last name or the first name then I want those as a result so let's go ahead and save that compile it just to make sure again I didn't mess up anything it's going to click on View and now I should be able to do say Thomas and sure enough there we go there's ax and Thomas again okay but this time it grabbed it from the first name so we could do that or we could do Anderson again that's the last name of Elizabeth so there we go it works for both either the last name or the first name and we get the right results so that's perfect now some of you may not be using a continuous form for your results and instead you are using a list box okay so rather than this continuous form let me just shrink this down instead of using a continuous form you've got a list box you can do essentially the exact same thing on your list box as we did with the continuous form so let's go ahead and just for a demonstration here I've got my list box let's rename it to LST customers and let's set the row source equal to the customers table get those first four fields again drag and drop change my last name to ascending save that close that and we want to show in our list box four columns and I'm sure you probably got you know your formatting of how wide you want your list box and all that good stuff I'm gonna go ahead and change this to size ten just to make sure I got enough room all right let's go ahead and view this sucker so there's our list box right and this is very similar to the continuous form and so that's why some of you might actually prefer the list box over a continuous form and this is perfectly fine we can use the we can still do essentially the same thing on this list box as we did to the continuous form so rather than setting the this record source property of the the continuous form instead we're going to change the row source property of our list box and that will essentially do the same thing so let's go to our search button go to our on click event and I'm just going to go ahead and comment out these two things here because we're not sitting on a record source anymore instead we want to change the list box of our customers I'm gonna change the row source equal to the sequel query and then again we want to do the same thing list customers re query okay it's really identically the same thing it's just a different property on the list box than it is from the from our subform okay so let's go ahead and save that compile it make sure I didn't goof up anything let's go ahead and view this and now we can do Anderson do a search and there we go there's our Elizabeth Anderson and we could do axon there's Thomas axon we could do again Elizabeth I think that's the right way to spell it sure enough there she is right so we can do a whole bunch of things with this search keyword very similarly between the list box and our continuous form okay if you guys have any questions or comments please feel free to drop me a line in the comment section of this video I'll be happy to answer any questions that you guys might have and as always please feel free to like favorite subscribe tell your friends about this and hopefully I'll see you guys in the next video thank you so much

Channel: Programming Made EZ

Views: 308,599

Rating: 4.903254 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, advanced, software, microsoft access, table, query, form, code, coding, development, visual basic for applications, computer programming, microsoft SQL server, SQL server, programming language, How-to (Website Category), search, keyword, keywords, subforms

Id: N0X8Hg-Sm6A

Length: 24min 53sec (1493 seconds)

Published: Sat May 16 2015