How to Make a Search Form with Combo Boxes in MS Access
ฝัง
- เผยแพร่เมื่อ 21 ก.ค. 2024
- Users LOVE good search options when they are using a data entry form that has a lot of data on it. Knowing how to make a fast filter form is one of the most essential skills for a database application developer. In this example, we’ll create a simple filter form by using two combo boxes with selections for us to choose from. You’ll learn how easy it can be to make a really good search screen with interactive controls.
Related Videos:
How to Make a Search Form with Combo Boxes in MS Access
You are watching this video now!
How to Make Cascading Combo Boxes in MS Access
• How to Make Cascading ...
Bound vs. Unbound Forms and Controls in Microsoft Access
• Bound vs. Unbound Form...
How to Make a Listbox in MS Access Part 2: Multi-select
• How to Make a Listbox ...
How to Create In and Out Listboxes in Microsoft Access
• How to Create In and O...
How to Make a Multi-Term Search Form and Report in MS Access
• How to Make a Multi-Te...
How to Use Timers to Run Actions or Code on an Interval in Microsoft Access
• How to Use Timers to R...
How to Use Before Update in Microsoft Access
• How to Use Before Upda...
How to Make Changes to Text in a Textbox in Microsoft Access Using SelStart and SelText
• How to Make Changes to...
How to Add Combo Box Rows On-The-Fly in MS Access
• How to Add Combo Box R...
How to Run Code as Your MS Access Form Opens - On Open, On Load, and On Current
• How to Run Code as You...
How to Use On Close and On Unload with Microsoft Access Forms
• How to Use On Close an...
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziemackenzie.com/
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a TH-cam Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...
For developers looking for a new role, check out:
www.toptal.com/qKaO2b/worlds-...
Needing to hire technical resources for your project? Get the best:
www.toptal.com/qKaO2b/worlds-...
#SearchForm #MSAccess #ComboBoxes
• How to Make a Search F... - วิทยาศาสตร์และเทคโนโลยี
![ตีสิบเดย์ [FULL] | คู่รักแซฟฟิก "หลิงหลิง - ออม" จากซีรีย์ "ใจซ่อนรัก"](http://i.ytimg.com/vi/zs8AfphGn2w/mqdefault.jpg)
I'm so glad I went looking for a better way to do this rather than the archaic way I was doing it before! Worked great!
Awesome! Glad that worked :-)
Thank you so much. This is exactly what I was looking for. Excelent explanation. Clear concepts and easy (after you know how to do it). Great. Best wishes from Canary Islands (Spain).
I'm glad it helped you! Thanks for the feedback, cheers
@@seanmackenziedataengineering Make more & more access related videos.. sir.
Nice job! I have 6 combo boxes they want to search off of and couldn't get my head started on how to approach it, this helped a lot, ty
Hello Bryan! Do you mine explaining how you got the code to work for more then just two combo boxes? I’m trying to have 6 work at the same time to filter some data out. Any help is appreciated!!
Glad I could help!
@@Burnt_Toast1469 I actually went with a listbox for the 6 selections plus a text box that refreshed the listbox with each keystroke (added the RequeryForm to an On Change event). I just kept adding the same code 6 times over, that was it.
Sean, I was about to go insane trying to come up with a workaround to get this working, then I found your video, followed exactly the same procedure you used and worked FANTASTIC! I can´t thank you enough for this amazing video!
Great to hear! Glad it worked for you :-) cheers
Thanks Sean! This was a great help for me....Cheers!
Glad that it worked for you!
Well done. Nice and simple explanation. Thank you.
Glad you liked it, cheers
@@seanmackenziedataengineering Thanks again. Btw and sorry for the bother, I thought I got it right, but I seem to have a bug that I can't figure out, probably because of the different MS Access version I use. When I select the first combo field, it overwrites the corresponding value on the first record, and similarly, when I select the second combo it overwrites the corresponding value on the first record. I guess I have to go back to your video 😀😀😀
Another great Video. Can you do an advanced video showing how you can build a SQL using Combo boxes?
Great suggestion! That would certainly be an in-depth video, but worth it. Maybe a 2 part or something.
HI Sean, Thank you so much for this video as I have been pulling my hair out (what's left of it lol) on how to do this.
That's great! Good luck on your project. cheers
Hello Sean, Thank you so much for this fantastic video! It was exactly what I was looking for. Is it possible to do a similiar concept using a combo box that is from a query with 2 fields of data, the first a number and the second a string? I have the query set-up to only be showing the second field, the string. Or am I stuck seeing the number for the filter? Thank you so much.
Good question! You can certainly do a number and a string! You can set your combo box to just have one column - your text column. Then, set the column count to 1 and the Bound column to 1 and it will work as criteria. Remove the numeric column from the combo's query so it just has one column in the query.
Hello Sean. Thank you very much for this video. The code works excellent on the form I built. I have another form that I used this code with to create a date range and keyword search. I am having trouble getting the form to filter with both date range and keyword search. Right now it’s one or the other. Do you know how I can get the criteria to work together?
You may need something like:
If Len("" & Me!txtDateFrom) > 0 And Len("" & Me!txtDateTo) > 0) Then
Me!strSQL = "[MyDate] Between " & #Me!txtDateFrom & "# And #" & Me!txtDateTo & "#"
End If
Then add the second part as I show in the video. Hope it works!
Great video thanks. Is there any way that the two boxes can be linked so that the second box only shows the corresponding items to the selection of the first box. I am putting together a rIsk Taxonomy and when I select the hazard I only want the types of hazard associated with the hazard selected to show in the second combo drop down.
You bet! You can use this method: th-cam.com/video/at0uaGSnUco/w-d-xo.html
Hello Sean. Thank you for your video. I love it. I've done everything according to your instuctions. Works fantastic. I have a question. What if I want to make second filter dependened on first one? How can I do it? Like I filter with first combobox and the second one should contain data only of those lines selected in first one but not all of them. Thank you
found an answer in your another video. Thanks a lot.
Hey that's great! I'm glad it helped.
@@seanmackenziedataengineering Thank you, Sean. Is there any solution to dropdown combobox with arrow down key and selecting values in it with arrow down and arrow up keys?
Hi sean，thanks a lot for this tutorial and how can I make this work on fields number? like search for number
With a number, you will use the same syntax, without the '
With a date field, you will use # instead of '
strSQL = strSQL & "[MyNumberField] = " & Me!cboNumber
or
strSQL = strSQL & "[MyDateField] = #" & Me!cboDate & "#"
In other videos I also demonstrate *parameterized* versions of this, which is safer, if you're doing production level stuff:
strSQL = strSQL & "[MyNumberField] = [Forms]![frmMyForm]![cboNumber]"
You can try both and see what works for you!
@Sean Mackenzie Data Engineering
Hello Sean, amazing video! It was so easy to follow along and was at a good pace to follow.Great job. I was able to use the example you had and got it working, but I did have a question on how to add more combo boxes to filter a form. I’m having issues figuring that out and would appreciate some code example on how to do it!
Glad you enjoyed it! cheers
I'll add this here as I posted on the other video:
There are a few ways to do it. You can create a table with all of the names of your combos in it. When the after update occurs, you can loop through the list of combos and build your sql or filter string, checking each combo for an entry as you go. To loop through records try this: th-cam.com/video/7HckYjH_wg4/w-d-xo.html
A different way to do it is to make sure that you properly name all of your combos and text fields you use to build your filter string. Then, each time a selection is made, you can loop through the "control" collection on the form, and when you find a control with its name starting with cbo or txt, then check for a value in that control and add it to the filter using code similar to what you saw in the Combo Search Form video. Good ideas for a video - a little more in-depth :-)
u da best, dude!
Glad you enjoyed it!
Hello Sean, Like you said yourself this is what the users want. This is something i was looking for myself to Integrate in some Databases with Huge amount of records! Realy Fantastic !! Is there a way also to pass true the filter from the first Sql to the second in the records in the combobox. So the options will be reduced after the first filter combobox? I don't want to fix it in the query. Because then I always have to Use all the Comboboxes. Just an Idea to make it userfriendly. Thanks for the great video 's for many years now.
I'm glad it is working for you! To get your desired effect, you need to use cascading combo boxes: th-cam.com/video/at0uaGSnUco/w-d-xo.html
This is a really cool tip but only works for single or continuous forms, I couldn't get it to work properly with split forms.
Interesting.. Do you mean on two different forms that are both open? Or a form on a subform?
Thank you
Welcome!
Hello, thank You for sharing Your knowledge. Is it possible to apply this solution so the data in one filter depends on other? For example: I have lots of production orders with assigned no of priority. I apply filter on priority and in combobox to filter by production orders i need to see only production orders with applied priority. Can You help?
Great question! Most certainly, you can do it: th-cam.com/video/at0uaGSnUco/w-d-xo.html
@@seanmackenziedataengineering Thank You for answer. I have applied it and it works! However, if You had the same name of the city in many countries, is it possible to show in cboCountry only countries that include this city? Basically, is it posibble to filter the list like You do in Excel?
Hello Sean, if you're adding a 3rd combo box do you have to use AND AND? Sorry for the question as still learning Access
its ok, forgot to add the event procedure after update in VBA ;-)
Yes, if you have a third combo box for filtering another field, you will create a similar structure and use AND again. Good question, thanks!
@@deltapixels hi thanks for asking this question how, i am ultra new to access too how did you write the and and line as im struggling :)
Thanks!
Thank you too!
This is so helpful, thank you very much. Is it possible for you to show what the "And" part of the code would look like if you were to use a 3rd combo box? Everything i'm trying is not working. Thank you.
At 16:47, copy the entire second If Then.. End If block (just before the "Add more fields.." comment in green) and paste it just after the "Add more fields.." comment. Then, change the names of the combo boxes in what you pasted to your new combo box name. Also change the field name to the field that will be filtered.
Repeat for a fourth, fifth combo etc. Let me know how it goes!
@@seanmackenziedataengineering ah I was over complicating it, thank you so much!!
@@tamarah7993 you're welcome! cheers
When I do this the Me.FilterOn = True gets highlighted in yellow. Your help is so appreciated!
Hi Sean, i have followed your instructions but its not working, so i have done something wrong and cant figue it our, could you assist plrase
What kind of error do you get?
Hi sean，how can i add a fuzzy search function base on your code？
That's a great question. At the most basic level, you could change a Like instead of = for your search clause. Using code, you could use more complex searches, possibly using regex or other methods. Great topic for a video!
For a simple like comparison, something like:
"[FieldName] Like '*" & Forms!frmMyForm!cboMyCombo & "*'"
If you're using SQL Express, SQL Server, or Azure SQL as a backend, use % instead of *
Hello, how can I get the same result but pop it in a table format? (Its working but I'm looking for a better view experience)
Thx, nice videos!
Try switching the form to Datasheet view in properties. If the header does not work as is, you can try putting your form as a subform on a form in Form view. Size your subform the way you need and then move your controls and code to the parent form (to work with the subform).
@@seanmackenziedataengineering thanks! I'll try! Also, I have 2 questions. First how can I add a filter with a numeric column (month), what do I have to change in the code? Second, Is it posible to set a little box that receives the sum of another numeric column (value in my case)?
Hello..my first filter box work but my second one does not...i keep getring an error on the me.filter =strSQL
Can you copy your code and paste it in here? Let's take a look!
@@seanmackenziedataengineering
Option Compare Database
Private Sub cmdfiltermonth_AfterUpdate()
requeryform
End Sub
Sub requeryform()
Dim strSQL As String
strSQL = ""
If Len("" & Me!cmdfiltermonth) > 0 Then
strSQL = "[month] = '" & Me!cmdfiltermonth & "'"
End If
If Len("" & Me!cmdfilteryear) > 0 Then
If Len(strSQL) > 0 Then
strSQL = strSQL & " and "
End If
strSQL = strSQL & " [Expiration year] = '" & Me!cmdfilteryear & "'"
End If
If Len(strSQL) = 0 Then
Me.FilterOn = False
Else
Me.Filter = strSQL
Me.FilterOn = True
End If
End Sub
Private Sub cmdfilteryear_AfterUpdate()
requeryform
End Sub
Thank you very much for this video! I have followed it and am able to make a search form based on a combo box. However, I am running into the following issue: If I have fields "Active" and "Inactive" it will filter "Inactive" no problem, but it will not filter "Active" (my guess is because the word Active is within the word Inactive). Likewise if I have "PX", "Not PX", "Some PX" and "Returned", when I select the last 3 options it will filter fine, but if I select "PX" it will also find the next two (it only filters out "Returned"). Is there away to get around this other than renaming the options in your fields to not be entirely within the other options?
It looks like you are using Like instead of = as I showed in this other video: th-cam.com/video/yFCBWH8YHjk/w-d-xo.html which is fine and will operate as you are experiencing. However, if you use = as I show in this video we're commenting on, you will be able to filter Active and Inactive, no problem. Let me know if you still have an issue after!
@@seanmackenziedataengineering That worked! Thank you so much!!
@@sarak4617 awesome! no problem :-)
Hi Sean, I tried your methodology and it worked on my database. I did add a LIKE command for an extra combobox and it doesn't work. This is what my command looks like below. MYpct is a literal %. If I enter HARE, it seems it should resolve to [Report_Title] LIKE '%HARE%'. Is there any way to interrogate what the resolved code is?
strSQL = strSQL & " [Report_Title] LIKE '" & MYpct & Me!cbo_ReportTitle & Mypct & "'"
You bet! Yes you can see what you get. Just after this line of code, you can do:
Debug.Print strSQL
After you try your code, just use Ctrl+g and the Immediate window will come up with your SQL string to check out.
Is there a way to lock the table so no one messes with the data but keep the combo boxes unlocked?
Yes, in the form's (or subform) properties, you'll see Allow Edits, Allow Delete etc. Adjust these and you can stop people from changing the data while using that form.
How to make search box as(while) we type at that time filter form(on that second filter form)??
Thanks, I'll add that to my list for future videos.
@@seanmackenziedataengineering Thanks you for your kind reply 👍👍👍👍 and effort for help us to become developer 👍👍👍👍
thank you very Mach im haitham from iraq
Welcome!
Hello Sean, Thanks a lot for your videos. These are great and easy to learn and improve.
I Have a problem for which I am searching everywhere for a solution, but till now....Nothing!
I have a table for my films. For each film, I have several information. Such as Director or IMDB rating and etc. For each film I input 3 actor names.
(Problem Starts here!)
Using this technic and VBA codes in this nice video, I could make different filters to search for films by the 'Director name' or by 'Country of Production'. But I don't know what to do with "Filter By Actor"??? I mean, I don't know how to deal with the "Row Source" for this Combo box since there are 3 fields (columns) to store the Actor/Actress names.
I tried to make a Query to gather all those 3 fields, in only one (1) field, so that I can assign the row source of the filter combo box to that query, but I wasn't successful.
I hope I could explain properly the issue.
Thanks again Sean
Best Regards
Great question! You can solve your problem by adding an OR to your filter string. Something like:
If Len("" & txtActor) > 0 Then
If Len(strSQL) > 0 Then
strSQL = strSQL & " And "
End If
strSQL = strSQL & "(ActorField1 = '" & txtActor & "' Or ActorField2 = '" & txtActor & "' Or ActorField3 = '" & txtActor & "')"
End if
That will solve your immediate combo search form problem.
However - this is a great opportunity for you to learn the value of normalization. This is one of the things that makes database systems powerful. What if you had one table for movies, one table for actors, and one table that just related actors and movies? This is called a junction table and you can learn about it here: th-cam.com/video/txQhw6Fk5MM/w-d-xo.html
One big bonus: You can have no limit on actors assigned to each movie, meaning you can list them all!
Good luck!
@@seanmackenziedataengineering Dear Sean,
Your quick response is highly appreciated. Thanks
About the codes: I am sure they are fantastic and can solve the issue If Only I knew what to assign in the "Row Source" of the Combo box! I Mean, first I have to put some data in that list and next input the above code to make the combo to filter among them. Or Am I wrong?
and about the Normalization: Totally agreed. As a matter of fact my database has a table for Films, one for Actors, one for Directors and etc. and all were related properly. But my weakness in Access made me suffer so I remove the connections and start working directly on one table!
Imagine I wanted to enter a new film+all it's information. I couldn't input the Director or any Actor, unless I input them first in their own tables first, then open the film table and make a new entry. I know there is a solution for this kind of problems (a code for "On Not In List" event of the combo box) that can add the data directly to it's table, but I don't know how to do it yet.
@@rezamohsenzadeh Cool project! You can type into the RowSource something like:
Select Actor1 As Actor From MyTable Union Select Actor2 As Actor From MyTable Union Select Actor3 As Actor From MyTable;
Try that first.
If it does not order correctly, try adding Order By Actor to the end (just before the semicolon).
@@seanmackenziedataengineering Great !!! It is working properly.
Thanks Sean. You are a savior angel!!!
By the way, is there any of your videos about the " Not in List" issue of the combo box and 'add to the table' action so I can learn something more from you?
@@rezamohsenzadeh That's great! Good job. In your case, the list will update each time you add a new actor in the text fields. However, you need to click Refresh All on the toolbar to see them. You can set Limit To List to No in properties, then enter a new actor as you work, then click Refresh All. Check the list and they should be added.
This video shows how to add these on the fly, but situation is a little different than yours. th-cam.com/video/aaDpib_mYRA/w-d-xo.html
Hello, does anyone know why I get parameter value every time I enter any form of the values in the combo boxes? I can’t seem to work it out, and whenever I do enter values, no data is show
Interesting - can you post your code to see? We can take a look
@@seanmackenziedataengineering haha hi Sean, I actually was doing this for some work and our database developer identified the issue, I had just misspelt the cbo! Thank you though for replying
@@lewisgibbs9360 Glad you figured it out! cheers
Hello. Elegant solution, but, sadly, it didn’t work for me :( I dis everything you did, changed the cbo name and the [], but nothing :(
Interesting.. Can you post your code?
@@seanmackenziedataengineering It gets better! I have a main menu with lots of locations (continuous form). I select a location, I press a View button and it opens some issues of that location (another continuos form). Each issue has a detail button and it opens a subform. Anyhow, thr code works perfectly in the main menu as you wrote it, but it doesn’t in the second form, the one with the issues. I’ll come back with the code as soon as i get to my laptop. Thanks!
😍😍😍😍🤩🤩🤩🤩
Thx!
Nice try, but I could not get this to work no matter what.
That's too bad! Did you get an error? You can post your code if you want.
Hello Sean, imagine using a combobox to search, made like the one that you use "Country", but instead having just one field has also the CountryID. How can I use the Sub RequeryForm to clear the filter from that search after select the record that I want in that field. In my case is to fill the field with data.
I have a "combobox search as you type" implemented in your cascading ComboBoxes video th-cam.com/video/at0uaGSnUco/w-d-xo.html to filter to the "country" that I will use in that field, but when I go to another record in the form the filter continuous on. I want to clear the filter from two "combobox search as you type" that I have in the same form.
Thanks,
It sounds like you want to reset the cascading combo boxes after you made a selection in the row. For that, you can use the After Update event on the combo box to set the RowSource of the two boxes back to the original sql string. You can use this one for After Update: th-cam.com/video/stQhrFY4k8E/w-d-xo.html
Thanks!
Welcome!