How to filter a subform from a textbox on main form (MS Access)

hi youtube this video is going to be how to filter a subform using a text box on a main form in microsoft access so when you start out you are going to have a table like this or multiple tables and these are going to be what we base our query off and then you're going to go into query design and here first thing you want to do is set output all fields to yes and then you want to go down here you can add whatever tables you like in this query it works the same way um but in this case i'm just going to use one table so down here the only fields you need to include are the ones which have criteria on them since we're outputting all the fields here we don't need to specify here down in this area that they're all included so for this video i'm going to show you how to um do searches which apply to two columns on one table so basically all you gotta do is put in the two columns or give me any number of columns one or three or five that you want to search and in the criteria you're going to put like asterisk amps and take search and other asterisk so basically this syntax here this is what you need this text search this is variable you can change this this is the name of the text box that's going to be on the form so you can make that whatever you like but i'm making a text search the asterisks is on either side they are just saying basically a random string before or after what's in text search so then you're going to copy paste this line onto the or line in the second column um for the other fields you want to search and that is basically the entire query so i'm going to save this go q i'll just call it stock okay so now we have that query next thing we need to do is create the form so oops so i'm going to go to create form design um set this to something nice and go from header i'm just gonna put a title here and we'll call it check okay all right so that's there and now i'm just going to chuck in a little bit more i should actually put make this a text box because this will be the search box so what we can do is extend that out extend this out too and paste what you would like and to say next to it i'm just going to make it black so it's a bit easier to see okay so that's there now the name of this part of the text box this is just the label um it's not really it doesn't really matter basically this is what matters so the actual text box itself you want to change the name to whatever you specified in your query for the like criteria so in my case it was txt search so i'm just going to repeat that yeah so you go to all in the properties which can be reached by right clicking going properties and txt search so the next thing is to make the sub form so go to design subform is here i'm just gonna drag it like that okay so this opens the wizard and it will tell you it asks you what you'd like to use you say existing tables and queries pretty select the one that we made for this copy them all make a name i'm going to call this stock sub so it's a sub form and this can just be okay so if i go into form view that's looking okay change it a bit okay so basically we have our subform here now and all this is showing is this is not showing what's in the materials table this is showing what the q stock query is returning which is taking from the materials table now since the criteria which is the text search box since the text search box currently is blank it simply returns every result because if we take a look at this criteria again um these asterisks basically make it so that it's returning something with any random string before and after what's in the search box so if there's nothing in the search box then of course anything before and after it will be encountered as abiding by the criteria so that's why we see all of them here um now um the next thing i'm going to do i'm going to add some a couple buttons because they can make it a little nicer to use each time you make a button it'll open the wizard but for this example i'm just going to cancel it each time okay so this can be um it's going to search this here can be show all so same thing as the text box because we're not going to be doing something with these buttons we want to rename them to some nice naming scheme so i'm just going to make it cmd for command search yeah and cmd sorry cmd show yeah okay so we have those now how it is now is if you type something in and press enter it will return it will basically return whatever results match that and regardless of uh it will show the results that match that and the columns that you specified in the query so you can do the same thing for the other column um let me just fix this okay um that right back okay so to get these buttons working all you want to do is be in design view go to these buttons with the property sheet open under event go to on click click these dots choose mac not macro builder choose code builder this will open visual basic so here for the search button we want to type is me dot the name of the form dot form dot required that's that and then for the show all button it's quite similar that re-query line will still be there except oh this is not correct actually this should be um the sub form not the main form because me refers to the current form and then this refers to the sub form which is inside the control here we go uh me dot txt search value equals quotes this just sets the value to be blank which as i explained before will return all of the values so if i save this and close it go to form view cn press enter that works press enter like that that works abs search that works show all clears the text box and shows all of them again if i go fh search so yeah basically you can see it works and obviously this is not very good looking but hopefully i've explained it well enough that you could do this on your own so yeah thank you for watching and please leave a thumbs up if this helped you

Channel: ms91

Views: 3,256

Rating: 4.8095236 out of 5

Keywords:

Id: VxMLcZWhSOo

Length: 11min 17sec (677 seconds)

Published: Sun Jul 26 2020