By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,117 Members | 1,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,117 IT Pros & Developers. It's quick & easy.

Base one combo box results on selection of another

100+
P: 184
Hi all

Now this seems staright forward, but I am having loads of problems getting this to work.

What I want to do is to have two combo boxes . The user makes a selection in the top one and then only data relavant to the selection is then displayed in the combo box below where the user can make another choise.

For instance :

I have one table - VehicleMakeAndModel

It contains the fields - ID,Make,Model

So ID will be a autonumber
Make will be like BMW,KIA,NISSAN,AUDI etc.
Model will be like 3 SERIES,SPORTAGE,ALMERA,PRIMERA ETC.

So the user selects lets say Nissan from the top combo box of a list of available Vehicle Makes.
The combo box below should then autopopulate with Models on relevant to Nissan like Almera,Primera etc. The user should then be able to select a model from this list.

Note that I use a ODBC link to a AS400 system, so I cannot use a foreign key in my table, hence I have the table set up as above.

Hope this info helps.

Thanks in advance
May 17 '07 #1
Share this Question
Share on Google+
7 Replies


missinglinq
Expert 2.5K+
P: 3,532
The concept you're talking about is called cascading comboboxes. Here's a link to a tutorial by Rabbit, one of our resident gurus, on the very subject:

http://www.thescripts.com/forum/thread605958.html

Good Luck
May 17 '07 #2

100+
P: 184
Thanks buddy

I will have a read throught it and see if I can apply it. ( I am having a few problems in whole as I am talking toa AS400 server and I cannot set this up the same as you would a relational database)

Thanks
May 17 '07 #3

100+
P: 184
Hmm

I had a go at adapting the code slightly ( As I am only using one key field to identify both Make and Model.) , but cannot get it to work.

I also thought of doing two seperate tables, one for Make(with it's own unique ID and the other for Model(also with it's own unique ID number) . But then I run into the problem where I cannot use foreign keys on the AS400 .

So I am not sure how to tackle this now.....
May 17 '07 #4

puppydogbuddy
Expert 100+
P: 1,923
You should be able to do what you want with your current structure. You just need slightly different queries as your row source, along with the appropriate AfterUpdate Event code for each combobox. Your queries would look something like this (replace the object names I used with the actual names for those objects in your database:

The autonumber will be the bound column of each combobox (set at 0 width to hide it)

Combo#1:
Expand|Select|Wrap|Line Numbers
  1. Select Autonumber, Make, Model From YourTable
  2. Order By Make, Model
  3.  
Combo#2:
Expand|Select|Wrap|Line Numbers
  1. Select Autonumber, Model, Make From YourTable
  2. Order By Model, Make[/font
  3.  
May 18 '07 #5

100+
P: 184
Howzit puppydogpuppy

Thanks for all that. Right I did as you suggested, but I am afraid not the right result.

I now have in the 1st combobox(Vehicle make) , all the duplicate make entries listed ie. 3 x BMW, 5 X AUDI, 2 X KIA etc.

If I do select a make of car, in the 2nd combo box(Vehicle Model) I get a list of all different vehicle madels instead of the models that just apply to the relevant make.

Here is an example of my table :

Vehicle ID Vehicle Make Vehicle Model
01 BMW 3 SERIES
02 BMW 5 SERIES
03 NISSAN ALMERA
04 NISSAN PRIMERA
05 KIA SPORATAGE
06 KIA SORENTO




Hope this helps.

Thanks
May 18 '07 #6

100+
P: 184
Right I have found a workmethod which I have implemented and I think it works, but I am unable to see the values being displayed the comboboxes. When clicking the dropdown, the available options is blank, but there is data being uploaded as if I change the code slightly for it to error, the error shows me the SQL syntax with relevant vehicle details seletions that was made.

Here is the link : http://www.fontstuff.com/access/acctut10.htm

This is odd.
May 18 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
Howzit puppydogpuppy
Thanks for all that. Right I did as you suggested, but I am afraid not the right result. If I do select a make of car, in the 2nd combo box(Vehicle Model) I get a list of all different vehicle madels instead of the models that just apply to the relevant make.
bloukopkoggelmander,
Referring back to my previous post, the reason you did not get the right result is because the Select statements I gave you were slightly incomplete. See the corrected ones below, and don't forget to replace the object names that I used with the actual names of the objects in your database. And you also need to put the AfterUpdate event code as shown

Combo#1 RowSource:
Expand|Select|Wrap|Line Numbers
  1. ”Select Distinct Autonumber, Make, Model From YourTable Order By Make”
  2.  
Combo#2 RowSource:
Expand|Select|Wrap|Line Numbers
  1. “Select Autonumber, Model, Make 
  2. From YourTable
  3. Where Make = YourCombo1
  4. Order By Model”
  5. Private Sub YourCombo1_AfterUpdate()
  6. Me.YourCombo2.Requery
  7. End Sub
  8. Private Sub YourCombo2_AfterUpdate()
  9. Me.YourCombo1.Requery
  10. End Sub
  11.  
May 19 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.