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!
@seanmackenziedataengineering Жыл бұрын
Awesome! Glad that worked :-)
@raulsansores6271 Жыл бұрын
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!
@seanmackenziedataengineering Жыл бұрын
Great to hear! Glad it worked for you :-) cheers
@antonioj3582 жыл бұрын
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).
@seanmackenziedataengineering2 жыл бұрын
I'm glad it helped you! Thanks for the feedback, cheers
@SharifulIslamBD2 жыл бұрын
@@seanmackenziedataengineering Make more & more access related videos.. sir.
@BryanDoesCinema2 жыл бұрын
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
@Burnt_Toast14692 жыл бұрын
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!!
@seanmackenziedataengineering2 жыл бұрын
Glad I could help!
@BryanDoesCinema2 жыл бұрын
@@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.
@stelkentritas43722 жыл бұрын
Well done. Nice and simple explanation. Thank you.
@seanmackenziedataengineering2 жыл бұрын
Glad you liked it, cheers
@stelkentritas43722 жыл бұрын
@@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 😀😀😀
@BobFeasey19 ай бұрын
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.
@seanmackenziedataengineering9 ай бұрын
You bet! You can use this method: kzfaq.info/get/bejne/l9pgqMR4uNS5k6M.html
@RungeCarl2 жыл бұрын
Another great Video. Can you do an advanced video showing how you can build a SQL using Combo boxes?
@seanmackenziedataengineering2 жыл бұрын
Great suggestion! That would certainly be an in-depth video, but worth it. Maybe a 2 part or something.
@No8s. Жыл бұрын
Hi sean,thanks a lot for this tutorial and how can I make this work on fields number? like search for number
@seanmackenziedataengineering Жыл бұрын
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!
@loganwinheim76952 жыл бұрын
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?
@seanmackenziedataengineering2 жыл бұрын
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!
@andreasvanbergen96962 жыл бұрын
Thanks Sean! This was a great help for me....Cheers!
@seanmackenziedataengineering2 жыл бұрын
Glad that it worked for you!
@kiransalave23 күн бұрын
After serching two days, I got the perfect one. And it work on string not on number & for the number what could be changes in code or can you suggest your videos Sir.😍
@seanmackenziedataengineering22 күн бұрын
Nice! All you have to do is remove the ' in the strSQL line of code.
@deltapixels2 жыл бұрын
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.
@seanmackenziedataengineering2 жыл бұрын
That's great! Good luck on your project. cheers
@nemo93962 жыл бұрын
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.
@seanmackenziedataengineering2 жыл бұрын
Interesting.. Do you mean on two different forms that are both open? Or a form on a subform?
@tamarah79932 жыл бұрын
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.
@seanmackenziedataengineering2 жыл бұрын
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!
@tamarah79932 жыл бұрын
@@seanmackenziedataengineering ah I was over complicating it, thank you so much!!
@seanmackenziedataengineering2 жыл бұрын
@@tamarah7993 you're welcome! cheers
@yasminehealey7687 Жыл бұрын
When I do this the Me.FilterOn = True gets highlighted in yellow. Your help is so appreciated!
@estebanaranda6353 Жыл бұрын
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!
@seanmackenziedataengineering Жыл бұрын
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).
@estebanaranda6353 Жыл бұрын
@@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)?
@abdulhamidalhaddadi22552 жыл бұрын
Thank you
@seanmackenziedataengineering2 жыл бұрын
Welcome!
@Hrakkar Жыл бұрын
u da best, dude!
@seanmackenziedataengineering Жыл бұрын
Glad you enjoyed it!
@timnorov2 жыл бұрын
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
@timnorov2 жыл бұрын
found an answer in your another video. Thanks a lot.
@seanmackenziedataengineering2 жыл бұрын
Hey that's great! I'm glad it helped.
@timnorov2 жыл бұрын
@@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?
@cynthiaramirez9714 Жыл бұрын
Hello..my first filter box work but my second one does not...i keep getring an error on the me.filter =strSQL
@seanmackenziedataengineering Жыл бұрын
Can you copy your code and paste it in here? Let's take a look!
@cynthiaramirez9714 Жыл бұрын
@@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
@jpaesen66 Жыл бұрын
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.
@seanmackenziedataengineering Жыл бұрын
I'm glad it is working for you! To get your desired effect, you need to use cascading combo boxes: kzfaq.info/get/bejne/l9pgqMR4uNS5k6M.html
@wubruce1026 Жыл бұрын
Hi sean,how can i add a fuzzy search function base on your code?
@seanmackenziedataengineering Жыл бұрын
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 *
@chantel43532 жыл бұрын
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.
@seanmackenziedataengineering2 жыл бұрын
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.
@lion_0077 Жыл бұрын
How to make search box as(while) we type at that time filter form(on that second filter form)??
@seanmackenziedataengineering Жыл бұрын
Thanks, I'll add that to my list for future videos.
@lion_0077 Жыл бұрын
@@seanmackenziedataengineering Thanks you for your kind reply 👍👍👍👍 and effort for help us to become developer 👍👍👍👍
@deltapixels2 жыл бұрын
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
@deltapixels2 жыл бұрын
its ok, forgot to add the event procedure after update in VBA ;-)
@seanmackenziedataengineering2 жыл бұрын
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!
@jasongreen30872 жыл бұрын
@@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 :)
@Burnt_Toast14692 жыл бұрын
@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!
@seanmackenziedataengineering2 жыл бұрын
Glad you enjoyed it! cheers
@seanmackenziedataengineering2 жыл бұрын
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: kzfaq.info/get/bejne/ba6TnrybrcXbl2g.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 :-)
@sarak46176 ай бұрын
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?
@seanmackenziedataengineering6 ай бұрын
It looks like you are using Like instead of = as I showed in this other video: kzfaq.info/get/bejne/r6xzdbp5nb-smp8.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!
@sarak46176 ай бұрын
@@seanmackenziedataengineering That worked! Thank you so much!!
@seanmackenziedataengineering6 ай бұрын
@@sarak4617 awesome! no problem :-)
@user-xp4kz4mu6s Жыл бұрын
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?
@seanmackenziedataengineering Жыл бұрын
Great question! Most certainly, you can do it: kzfaq.info/get/bejne/l9pgqMR4uNS5k6M.html
@user-xp4kz4mu6s Жыл бұрын
@@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?
@petermayall38373 жыл бұрын
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
@seanmackenziedataengineering3 жыл бұрын
What kind of error do you get?
@BryanDoesCinema2 жыл бұрын
Is there a way to lock the table so no one messes with the data but keep the combo boxes unlocked?
@seanmackenziedataengineering2 жыл бұрын
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.
@tamarah79932 жыл бұрын
Thanks!
@seanmackenziedataengineering2 жыл бұрын
Thank you too!
@wickedOneDude Жыл бұрын
Hello. Elegant solution, but, sadly, it didn’t work for me :( I dis everything you did, changed the cbo name and the [], but nothing :(
@seanmackenziedataengineering Жыл бұрын
Interesting.. Can you post your code?
@wickedOneDude Жыл бұрын
@@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!
@user-bz7lk5su3j2 жыл бұрын
thank you very Mach im haitham from iraq
@seanmackenziedataengineering2 жыл бұрын
Welcome!
@rezamohsenzadeh2 жыл бұрын
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
@seanmackenziedataengineering2 жыл бұрын
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: kzfaq.info/get/bejne/qt6Bm9pnq9GZfYE.html One big bonus: You can have no limit on actors assigned to each movie, meaning you can list them all! Good luck!
@rezamohsenzadeh2 жыл бұрын
@@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.
@seanmackenziedataengineering2 жыл бұрын
@@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).
@rezamohsenzadeh2 жыл бұрын
@@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?
@seanmackenziedataengineering2 жыл бұрын
@@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. kzfaq.info/get/bejne/l8d0o8yTxNO9gnU.html
@gordongeber39997 ай бұрын
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 & "'"
@seanmackenziedataengineering7 ай бұрын
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.
@lewisgibbs9360 Жыл бұрын
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
@seanmackenziedataengineering Жыл бұрын
Interesting - can you post your code to see? We can take a look
@lewisgibbs9360 Жыл бұрын
@@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
@seanmackenziedataengineering Жыл бұрын
@@lewisgibbs9360 Glad you figured it out! cheers
@barryhandyman69852 жыл бұрын
Nice try, but I could not get this to work no matter what.
@seanmackenziedataengineering2 жыл бұрын
That's too bad! Did you get an error? You can post your code if you want.
@shafiqrehman7614 Жыл бұрын
😍😍😍😍🤩🤩🤩🤩
@seanmackenziedataengineering Жыл бұрын
Thx!
@ambrandaoyoutub11 ай бұрын
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 kzfaq.info/get/bejne/l9pgqMR4uNS5k6M.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,
@seanmackenziedataengineering11 ай бұрын
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: kzfaq.info/get/bejne/qdqBm9V3vprPaHk.html