Full Version: Dynamic Combo box
UtterAccess Forums > Microsoft® Access > Access Forms
claussoegaard
I'm trying to create a form that, among other things, has two combo boxes. CB1 and CB2 we'll call them.
I want the contents of whatever I can choose between in CB2 to be determined by whatever I choose in CB1. For instance: If I want to store data about what car a person is driving, CB1 will be "Make" and CB2 will be "Model." If I choose "Chevrolet" in CB1, I only want the options in CB2 to be "Trailblazer", "Tahoe" etc. and NOT "Corvette", "Mustang", "RAM" etc.. My tabels are structured in the database as follows (still with the car example):
erson(PersonID, Name, MakeID, ModelID) - I'm assuming ONE person can only own ONE car in this example.
Make(MakeID, Make)
Model(ModelID, Model)
Make-Mode-Relations(MakeID, ModelID) - Here I define which Models belong to which Makes..
Do you understand my question (I'm danish so I'm sure about my english) - and does anyone know how / if these combo boxes can be made?
Hope you can help.
Claus
polant
I think this is quite str8 forward.
irst I think you can do without the table Make-Model-Relations, since one model can belong to only on make. You can have the MakeID as foreign key in table Model, and have an one-to-many relation between tables make and model.
The first combo, CB1, will have as row source table "Make", so the user can choose the make of the car.
The second combo, CB2, will have as row source a query selecting records from table Model where MakeID = forms!FormName!CB1, i.e. select only the models made by the make selected in CB1.
You need to put the following code in CB1's after_update event:
Me!CB2.Requery
Every time a new make is selected in CB1, CB2 will be updated to show the models of that make.
If you want to keep your existing 3 table setup, you need to change the row source of CB2.
Hope this is of help.
sleepy_chicken
Hi and welcome to UA!
This technique is called Cascading Comboboxes and there have been many posts here about that subject.
Search Forms using the phrase Cascading Comboboxes and you'll get many hits
HTH
MattJ
And a demo can be found here www.utterangel.com
TH
claussoegaard
Hi guys. Thanks for your help! It's working now. If I use the Form to create a new user, and i.e. select "Chevrolet" as the "Make" in CB1, it gives me stuff like "Trailblazer" etc as the options in "Model" in CB2. However - I still have a problem. Two actually:
ne:
I would also like to use the form to review whatever cars a user has. If I open the form, it starts on user no. 1. He has a Chevy Trailblazer, which is perfectly displayed here. If I go to user no. 2, I can see that he has a Chevy Tahoe. However, if I go to user no. 3, I can see that he has a Dodge, but the "Model" box is empty, all though he has a Dodge Viper, and that this is what the table says.
It seems that throughout the posts in the form, my selection in post no. 1 (Chevy) is applying to all the posts, and being that a "Viper" isn't a model af the Chevy, it isn't displayed. So only the chevy's display the model. If I go to user no. 2 and change his car fra Chevy Tahoe, to Dodge Ram - then I can see the models of all the people that has Dodges. Does my explanation of my problem make sense to you guys?
Problem two:
Imagine that user no. 1 changes his car from a Chevy Trailblazer to a Ford Mustang, and that user no. 2 changes his Chevy Tahoe, to a Chevy Corvette. I then open the form. I change the Make of user no. 1 to Ford, and then it perfectly changes the options in "Model" to stuff like "Mustang", "Explorer", "Focus" etc.. However, when I get to user no. 2 - who only changed the model, not the make - I don't change the make, but immediately want to be able to change the model from Tahoe to Corvette. First of all it doesn't even display that he has a Tahoe (problem no. 1), but the options I have, are still the Ford-models.. Which means that it remembers the choice made in user no. 1 - and this actually allows me to store data that says that user no. 2 has a Chevy Mustang... :-)
Sorry if I wrote too much.
What I did was according to what polant said.
CB2 says: SELECT mo.Model FROM Model as mo WHERE mo.MakeID=Forms!FormName!CB1;
and I placed the "Me!CB2.Requery"-code in CB1's after_update event.
Any tips?
Thanks
Claus
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.