Microsoft Access Cascading Combo Boxes - One Combo Box Selection Filters a Second Combo Box
HTML-код
- Опубликовано: 30 июл 2024
- In this video, I will show you how to set up cascading combo boxes. This is where the value selected in the first combo box will filter the results of the second combo box. For example, you choose a state in box one, and then box two will show only a list of cities from that state.
Liana from Honolulu, Hawaii (a Platinum Member) asks: When I'm entering in my customer's address, is there any way that I can pick the state first from a list of options, and then have the city combo box only show cities from that state? I have a very select list of city/state combinations that my company sells to, and it would be so much easier than typing these in every time. Thanks!
Silver Members and up get access to an Extended Cut of this video. Members will learn how to display cascading combo boxes in a continuous form, and also how to perform the cascade without the need of an external query.
MEMBERS VIDEO:
• Microsoft Access Casca...
BECOME A MEMBER:
RUclips: / @599cd
or My Site: 599cd.com/THMember
LEARN MORE:
599cd.com/Cascading
LINKS:
Relationships: 599cd.com/Relationships
Relational Combo Boxes: 599cd.com/RelationalCombo
Get Criteria from Open Form: 599cd.com/CriteriaForm
Intro to VBA: 599cd.com/IntroVBA
AfterUpdate Event: 599cd.com/AfterUpdate
OnCurrent Event: 599cd.com/OnCurrent
Zip Code Template: 599cd.com/ZIPCode
Subforms: 599cd.com/Subform
Access Beginner 8: 599cd.com/ACB8
COURSES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Contacts
Order Entry & Invoicing: 599cd.com/Invoicing
More Access Templates: 599cd.com/AccessTemplates
ADDITIONAL RESOURCES:
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/TechHelp
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...
KEYWORDS:
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, cascading combo boxes, city state select, link combo boxes, dependent on another combobox, multiple cascading combo boxes, cascading combo boxes in continuous forms, cascading combo boxes subform, choose from list of cities in state, afterupdate, me.requery, oncurrent, List Items Edit Form, synchronize two combo boxes
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
This has helped me more than my 2 textbooks and 1 month of study. Thank you! I greatly appreciate your approach and character in these lessons. Will be upgrading for more information.
Glad it was helpful!
Very helpful, Highly recommend to anyone who's going to study IT in a college or Uni. Helped a lot with my Database for one of my units. much appreciated.
Great to hear!
Amazing. I have been looking for this for 2 months until i found this video. Thank you.
You're very welcome!
Thankyou for your expertise. I have been using access/vga for a long time. It only goes to proof that you can teach an old dog new tricks
Thank Ian😀
Exactly!
I liked your video much and find solutions which are not available else. Thanks for your teaching and efforts
You are most welcome
Best Access teacher ever 💐
Thank you! 😃
You're so right.
That's what we pay him for lol
@@AustinLovesDrPepper1 I'm about to jump in at the Gold level as I am bringing a database to current for 1 of our long standing jewelers, that he initially created in dBASE, and someone else transitioned it to Access in 2013.
@@599CD one question, how to use cascading combobox in continuous forms????, because when I go to second records it's disturbing all other records
9:19 I appreciated it a lot. I didn't find anything similar in Portuguese language. Congratulations!
14:54 "On current" Após clicar para ir para o próximo registro.
Thank you! 😃
Thank you so much Richard. I just made a 4 levels cascade COmboBox, with 2 fields each (tag and title one). Now heading to create a a concatainer from the TAG field to create a code, similar to a serieal number. Stay tune for my progress
Nice work!
@@599CD still work, but I have a challenge now. I have to concatenate or display the result as one, in order forms or report.
Really good! Saved me since I couldn't find this in any of my textbooks.
Yeah from what I've heard from various students around the world the textbooks that they teach you guys from are sorely lacking
You super teacher in Access number one in my playlist😊
Thank you Sir, your videos have been so helpful to me. God bless you Sir 🙏
So nice of you
This tip is amazing. Just solved a major issue for me
Sweet
New subscriber and member here - this content is excellent
Thanks!!
Thank you Sir, fantastic content.
Like always … you are amazing …
This is definitely a very unique way of cascading combo boxes, something to look at. I love how you use the subforms. I would think that on the state form, the state ID isn't even necessary, no?
Correct. It's not. People like seeing it though, and when I'm teaching beginners, I like them to get used to the concept of that AutoNumber. Although I also try to emphasize that THIS NUMBER ISN'T FOR YOU... it's only for Access to make relationships. If you haven't yet checked this video out, give it a watch: 599cd.com/AutoNumbers - it shuts up anyone who argues AGAINST using AutoNumbers. I made this after arguing with some "self proclaimed" database expert that AutoNumbers aren't necessary.
Brilliant Video! Thanks!
YW
thanks a lot sir, that's awesome
OK, stop making that many videos! just kidding, really appreciate all the help! Saludos desde México!
It Helped me a lot.... Thank you:)
You're welcome!
Hello that REALLY good Thank You 😉
I'm glad you like it
Dang good stuff
VERY helpful!!
Thanks
3:30 Hi Richard! Love your 3:30 videos. Heres my question...
For my gymnastics center, I have two access forms in my database, one for athletes, one for parents. I create the parent profile first and save. Then i create an athlete profile and connect that athlete to a specific parent by use of a lookup. Of course parents can have more than one athlete. However, if a child drops a class , adds one, etc., it changes the monthly tuition and needs to be noted in the parent profile. I want to create a command in the athlete form that will take me specifcally to that athlete's parent profile to handle this rather than opening the general parent form and searching for the right record.
Thanks so much!
Coach Max
Nice video sir .... Thanks sir
Welcome
Great video's, I am new to access and your video's are being such a nice way to start learning. I got a question; I am figuring out cascading combo boxes.. but for continuous forms the query updates and removes the selected entries, my form is like the contacts form but then with cascading combo boxes. Is there any way to solve this? Or is the method just for single forms?
Kind regards, Bob
I cover that in the Extended Cut for members.
Thank you.
Welcome!
Thank You
You're welcome
nice video.. I've been following your educational videos and its helping us alot. One thing I've not found, is: suppose I have three comboboxes; combobox 1 has three or fours items. Is there a way I could filter the data, so that the selected value in the combobox one will NOT appear in the second combobox? I will appreciate your help my Teacher.
Sure. Set your 2nd query up so the criteria is NOT equal to.
Combo1
Thanks, that was very helpful. When I type the VBA code to requery the city combo box, it doesn't work. If I change the state, it doesn't refresh the cities. It only refreshes when I reload the form, as you would expect. The code is definitely saving. Access is definitely recognising the code because it suggests the requery function as I am typing in the code. I would be grateful for any advice.
Thank you very much! I'm wondering whether it's possible to set CityID and StateID as necessary in the customer table - like a normal foreign key.
Sure
Quick Question: In this example, its rather straight forward each city has a unique state it's associated with so you can use the StateID to filter the cities appropriately. For my use case, I have a list of Trinkets that have a unique ID that is saved in a larger database. Each Trinket belongs to a larger category. These categories have addons associated with them. When I setup my cascading combo box, I want the addons to be limited by the category associated with each Trinket. I suspect, in the Query that we put the form reference to there's a way to have the Trinket's Category value be the value to pass to the query, but I can't figure out the syntax. Any advice?
With a little more googling, I figured out a way to do this. I created a HIDDEN TEXT BOX that gets the CATEGORY value from my TRINKET Box. I passed this value over to the Addon box to make it work. A little hacky feeling, but if there is a better way... I'm all ears!
Yeah, that's kinda like what I do in the extended cut.
Thanks so much for the information! Now say we want the zip code to update based off the city we select? Specially I have a database where properties have only certain tenant's names pop up (Which this video addresses). Now I already had the table of the tenant and their unique IDs, I just want the form to update that in the relational combo box lesson. Maybe I'm overcomplicating it lol.
Sure, you could do that, but keep in mind that one ZIP code could have multiple cities in it, and one city can have multiple ZIP codes. See: 599cd.com/ZIPCode
I like this video. Is there any solution to dropdown combobox with arrow down key and selecting values in it with arrow down and arrow up keys?
Yes, you can use the arrow keys.
I there a way to may data base or form in access to look like full screen. Kind of power point. The idea is to make it look like an app or program instead of working within ms access GUI itself?
Dear respected sir your tutorials are helping me alot
I m coming across a problem
I want to populate picture data from main table in another table using vba using combo box but it doesnt work
Plz guide how do i fill an image
599cd.com/Images
Thanks much. Great videos. I have used this City/State cascading combo box, and created an Add New State or City Form. Using it for both Cities and States, but in the Edit Mode. But what I am trying to do (which I'm not figuring out) is when opening the frmAddNewCityState Form the City Combo Box, have it fill in the State automatically -- where I can then add new Cities just to that State -- without the need to cycle thru all the states until I get to the one I want to add cities too. Do you have any suggestions as to how to accomplish this? Thank you.
I do something similar here: 599cd.com/ZIPCode
Thank you! Your videos have been so helpful so far, so many to remember which one helps with what! I've set this up in a test form and test query and works 100% but when following the same steps in the form I want to use, there is a disconnect in requery. It updates the query but not the second combo box, if I click next record and go back again then it's updated the combo box. I have no idea where it's stuck! Any help would be appreciated! Thanks again.
Without seeing what you have, it's impossible for me to tell what's wrong.
@@599CD I ended up just redoing the whole form again and worked well. I’m sure there was some small property somewhere that was slightly different! Anyway I just signed up for silver membership, learning from your inventory videos currently. My only problem now is working out how to set up the tables, you’ll be getting another question soon enough!
@@599CD how to use cascading combobox in continuous forms???? Or continue sub form???
I want to ask question about cascading combo boxes per video available the layout of the forms are columar so it work perfectly but if the layout is Tabular when you select one it automatically select for other records or other line. And want to know if you can help me solve that problem. Thank You
If you're talking about a continuous form, then that's covered in the extended cut.
I have a situation where the Icon for the 'List Items Edit Form' doesn't disappear automatically when the combo box is closed. The icon stays on the screen until I switch records...
That's weird. I've seen Access leave screen fragments before in older versions, but not since 2016 or so. 599cd.com/Trouble
Is there a way to do this across multiple forms without creating a new query for each form it is used on? Like say you use it where you have it in the video in a customer form but then also using the same dropdown boxes in a continuous form to filter.
The state is being stored as the ID Number rather than the name. Is there a way to get it stored as the actual state name?
Well, I don't know if you needed an ego boost today Richard, here comes one. Years I've tried to get my head round this Access stuff. YEARS. I have many books that are each almost too wide to pick up with one hand at the spine. Some are called bibles, and are fatter than actual bibles. Bound them in leather and you'd call them tomes. You could stack them up and measure the height in feet and inches.
I've read every page of these manuscripts. Probably millions of words, over years. Filled multiple Moleskines with notes. I would not consider myself a super user by any means, but I am better than your average Access user I would say.
Anyway, point being, I watch your videos, even the real easy ones, and I still learn stuff. I watched all 4 hours of the absolute beginner video, and STILL learned things. So got myself a shiny membership and probably learned more skills in the last month than the last 4 years. Most importantly, for me at least, your method has ensured I retain this knowledge.
So just thought I'd say thanks for that. love your teaching style and technique.
Thanks, Callum. You've earned my "Comment of the Month" award.
Hello Richard, These comboboxes are all in relation to each other. However in the application that I have in mind some of information (records) will be deleted in the future due to the certain events. So when you delete a record your database will only show a number but you may never find the 'description' of that number again. So my question is can you do this cascading also without relations so that in the end only your plain selection will be in the database. (Pff maybe confusion explanation but I hope you get it).
599cd.com/Ask
hi sir, nice to watch thanks,. but when I open table there in state field has number . ?
Sounds like an ID
When the combo box is requeried, the source table keeps the original value unless it is changed through the combo box... Say I have a list of states but the city list is empty for some of those states, if you choose a state with an empty city list so there is nothing to choose from, the source table will not update its value for the city. How do you fix this?
Add cities for those states.
hi sir,
your video is very informative bu i have a question that how we can cascade combos in a continuous form as they create problems. because as we move to next record on same form and change value of a combo box it also changes previous combos too
solution please.....
That is covered in the Extended Cut.
Hi I'm Vinod. My question is how to connect two tables.
For example I'm having a master table which consists of all materials and I wanted to issue one item from that table. When I create a form, and enter the primary unique key, I need to get all the details filled in that form...is it possible
I'm sure it is possible. Sounds like you need DLookup and AfterUpdate. 599cd.com/AfterUpdate
Hi, how to make cascading comboboxes work on continuous forms? :(
SOS! I used this lesson because there was just 1 thing for my query I wanted to use. I only got as far as Expert 3, and tried to build a form where I choose a date, and another combo filters events (calendar terms, not 'access event') on a schedule. It works and filters, but something happened and now it won't allow me to click on the schedule event combo box. The filter still works, but I can't click it to update info. Does this sound like a familiar problem?
599cd.com/ask
Hello Mr. Richard. I've been running into a problem when I click Tab from the combo box, it switches the next record instead of going to the First Name entry field. Could you please help me fix it. Thank you. Also Thank you very much for all the videos
Never heard of that one before. 599cd.com/Trouble
In your form's design view you need to set the tab order between fields, if you don't you could end up with unexpected/unwanted results as you have discovered. It is more than likely covered in one of Richard's many videos.
Ask: how to related combo box from main form into combo box in subform so can create dynamic combo box betwen main form and subform ...thanks for your answer ...
Works the same, you just have to know how to refer to the different form names. 599cd.com/FormName
Thanks for the tutorial. I'm having an issue. I set up linked Country > State/Territory combo boxes on a subform. The main form contains organization info then the address subform is on a tab control with other tabs containing other info about the org (e.g., personnel; stats about the org, etc.). When I try this using the actual address subform, it works. However, when I open the main form and run the base query for the State combo box, I get prompted for parameter value. Not sure why this is happening. Any ideas? Thx.
Probably spelling, or you're not referring to the subform correctly: 599cd.com/EPV
Hi Rich, can you cover how to create cascading combo boxes on a continuous form? On a continuous form, when you change the parent combo box it will requery all of child boxes on the form rather than just the child box on the current record!
That's covered in the Extended Cut
@@599CD Awesome! I will check it out. Thank you!
I have followed this exactly, however my child combo box (the city selections), does not allow me to select other options, other than the top presented field. Example i can only select Buffalo, i cannot select Rochester or Syracuse. Where have i gone wrong and how do i fix this?
599cd.com/ask
i have a problem, the combobox cascading effect works pefectly, but when i save the records, instead of the city names and the state names i get the id's
That's because you're probably storing the IDs in the fields which is what you're supposed to do
thank you very much, but if we use it, the costumer table , will show 1,2,3 not the name of state. can you please tell me how to appear name of the state and the city?
You don't have your combo box set up correctly. You need to hide the ID field. 599cd.com/Relational
How do I make combobox drop automatically when I tab key to the field. I get tired clicking on the arrow of the combobox to see inside the combobox to make choice. Because the combobox has many items inside, would be nice if the dropdownlist drops and I can see and type the option. Example: I type 1 which is 1-Stopped treatment, 2-Completed treatment, and so for. There are 9 options, and I never memorize the 9 items to choose from. Sometimes I have to update 100 rows, and hurt the hands by clicking too much on the arrow and waste time too.
ComboBoxName.DropDown in the On Got Focus event.
@@599CD Thank you sir.
what if i want to add new record on my CityT? for example, add new record on New York state. should i write StateID first? any fastest way to autofill that StateID what we want to add? it's annoying, if i search it manually on StateT and have a bunch of data on it. i hope you can make a new video how to do that. thanks!
Personally, I like doing it this way: ruclips.net/video/5F6gYW91wBI/видео.html - get the ZIP first. But you could do it your way with a StateCombo and then that would filter the CityCombo so you only see cities from that state. I have a 3-level form (Country > State > City) in my SQL Seminar Part 2: 599cd.com/SQL2
Great video. when in datasheet view all the other city names disappear from the records I am not on, how do I get them to remain visable. I think its the requey code...
Don't use datasheet view. :)
hehe. I am new to access. My work would like to create a unique part number for every product we can make which consists of different combinations of materials and other info from different tables. The only way I can think of for creating every combination of items is using a datasheet view and manually creating each combination. Is this the right way???
How to do this with Lists allowing Multiple Values. e.g. First Box selction: France and USA -> Second Box Values available: Paris, Lyon, NY, LA, Boston ..but here aswell multiple selections allowed.
Case Scenario:
I wan't to sell Product A in multiple Countries but not in all cities of the selected Countries.
You could do it with Groups. See:
Access Expert 7: 599cd.com/ACX7
ABCD Part 2 (Groups): 599cd.com/ABCD2
Search Seminar: 599cd.com/SearchSeminar
I'll be doing something along those lines in tomorrow's TechHelp.
Is it possible to copy and paste these combo boxes onto another form? Instead of having to do the entire process all over again.
Absolutely.
I tried this in the subform of an orders form. However, everytime I open the orders form the criteria box for the subform pops up and I'm starting to think there is a problem with the Access program itself. Another DB I've been using for a year is now not working properly even though I haven't changed anything.
Without seeing what you've got there it's impossible for me to tell what's wrong
@599CD I understand now why my combo boxes weren't working. They are both in the same subform which apparently requires a few extra steps.
How do I make the combo boxes work in a subform?
That's covered in the extended cut
If you change the value in the 1st combo box the second looks blank (after re-query) but it still has stored the previous value in the table. Making it a required field doesn't help as it has the previous value stored. Is there a way to refresh the table value also? How can you solve this problem?
No worries I figured it out! (for anyone else reading you set the After Update event for combo box 1 to show: yourcombobox2=null on the line before the requery code. At least I hope that's right - it's working for now anyway! (I'm a beginner like most people on here)
Glad you got it working
Doesn't this allow for duplicate cities in the StateT?
If memory serves, the way that I have it set up is that you can't have a duplicate city for the same state, but you should be able to have the same city name in there twice as long as they're different states. But it's been a while since I recorded this video, so I don't remember. You'll have to watch it and find out! LOL.
If I joined today, my 30 days membership count will start from today itself?
I'm interested to join.
Honestly, I'm not sure. You'd have to ask RUclips. If you sign up on my web site, I can control it. 599cd.com/THM
No matter what I try the combo box populates but I cannot select anything in it. I've followed everything to a t, so annoying
Do you have it bound to a field? Is it locked? Is Allow Edits for the form set to YES?
@Computer Learning Zone, how to use cascading combobox in continuous forms???? Or continue sub form???
That's covered in the Extended Cut
@@599CD can you please provide link of that video??? It's really helpful for me, thanks
How to show Cascading Boxes result in input box. Pl help
I don't understand. You don't use an input box to show results. Those are for getting input.
Sir I want cascading combo box in continues sub form how can I do?
That's in the extended cut for members.
How can You connect 4 Combo boxes instate of 2?
Same way you did 2
The edit list option doesn't work, after the .accdb is converted into .accde file.
The same happens with list box too.....
Pls advice way forward....
That's funny the list items edit button works fine for me in an accde file
@@599CD may be there are some setting to be done or undone.... Can you pls check and help to understand.
It happens with combo boxes and list boxes...
It happens with unbound ones.
Pls help
😊
:)
No relationship established between customer table and city table?
Nope. I seldom use Global relationships. Only if I absolutely need to enforce referential Integrity.
@@599CD thank you
Cascading Combo Boxes and Split Forms?
I hate split forms - but you'll have the same problem with continuous forms. I cover that issue in the Extended Cut.
Too bad this doesn't work in datasheet view on your form.
Good Day Mr richard Pls I am have a small challenge. My City Query is not showing the data . its only show plain
OK
Ok what
Something has happened to a handful of videos. I was able to see similar videos to the one above last week, but now its just a bunch of lines along with your voice over.
You're the first and only person to complain about this, so I suspect it's an issue on your PC.
thank you very helpful
You're welcome!