Dependent combo boxes

[hrov]

hi
I have two options above a combobox. If option one is selected i want the combobox's criteria (or row source to be list 1) if option 2 is selected i want it to be list two. These two lists are from 2 seperate tables.

I am thinking that i need some code but dont know what the code should be. This simplistic formula shows what im looking for:

Row criteria = if option1 then list 1. If option2 then list 2.

Ps should i put any code into the row source?
Thanks for the help

 

[pbaldy]

The general concept is called cascading combos. I have a sample here:

http://www.baldyweb.com/CascadingCombo.htm

Sounds like you'd want to adapt the code method to use your two queries.

 

[hrov]

The general concept is called cascading combos. I have a sample here:

http://www.baldyweb.com/CascadingCombo.htm

Sounds like you'd want to adapt the code method to use your two queries.

thanks for the link but i cant download for some reason (something to do with adobe)

But i dont currently have any queries for this? looking at your code where would i substitute the names for option 1 and 2 aswell as combobox1? Thanks

 

[pbaldy]

If I'm understanding right, something like:

If Me.FirstCombo = "Whatever" Then
  Me.SecondCombo.RowSource = "SELECT * FROM Table1"
Else
  Me.SecondCombo.RowSource = "SELECT * FROM Table2"
End If

 

[hrov]

If I'm understanding right, something like:

If Me.FirstCombo = "Whatever" Then
  Me.SecondCombo.RowSource = "SELECT * FROM Table1"
Else
  Me.SecondCombo.RowSource = "SELECT * FROM Table2"
End If

Thanks Paul but im working with 2 option buttons (either option button one is selected or option box 2 is selected).

How would i incorporate this into the code, will i have to use something like if option1 is true then...... else select from table 2?

 

[pbaldy]

Presuming they're in an option group:

If Me.FrameName = 1 Then

 

[hrov]

Presuming they're in an option group:

If Me.FrameName = 1 Then

ok thankyou, where does this code go (its not working in the row source)

does this code look ok?

 "ifMe.option1=1 Then ";"Me.Firstcombo.Rowsource = 
""SELECT forename FROM swimmer details""";"Else";"Me.firstcombo.Row source 
= ""SELECT stroke FROM stroke""";"End if";" "

 

[pbaldy]

The code I posted in post 4, modified as above, would go in the after update event of the option group. That way whenever you changed selections, it would change the combo. I don't think you can accomplish what you're trying to do with a row source alone (not with 2 tables involved).

 

[hrov]

The code I posted in post 4, modified as above, would go in the after update event of the option group. That way whenever you changed selections, it would change the combo. I don't think you can accomplish what you're trying to do with a row source alone (not with 2 tables involved).

I am sorry but i still dont understand.

If Me.Option1 = 1 Then
  Me.Firstcombo.RowSource = [swimmer details]![forename]
Else
  Me.Firstcombo.RowSource = [stroke1]![stroke]
End If

Can you help correct my code please.

 

[pbaldy]

Based on the SQL you posted above:

If Me.Option1 = 1 Then
  Me.Firstcombo.RowSource = "SELECT forename FROM [swimmer details]"
Else
  Me.Firstcombo.RowSource = "SELECT stroke FROM stroke"
End If

You'd also need to adjust the name of the combo.

 

[boblarson]

Just to throw another question into the mix -

Do you have two separate option buttons WITHOUT an Option Group or are you using an Option Group? The fact that the code is checking Option1 makes me wonder. Normally you woulld check the OPTION GROUP value and not the individual option within it.

 

[hrov]

Based on the SQL you posted above:

If Me.Option1 = 1 Then
  Me.Firstcombo.RowSource = "SELECT forename FROM [swimmer details]"
Else
  Me.Firstcombo.RowSource = "SELECT stroke FROM stroke"
End If

You'd also need to adjust the name of the combo.

now its saying invalid syntax. im about ready for giving up

and i named the combo box firstcombo to try and make it easier

 

[pbaldy]

Can you post the db?

 

[hrov]

Can you post the db?

Its got some personal data though so will have to post it (and change data as a last resort).

I am now using a combo box instead of option buttons to make it simpler for me.
(its no longer saying invalid syntax)

BUT in the combo box where i want the desired information showing says:
"SELECT stroke FROM stroke1"

here is my code that I am currently using:

 If Me.combo1 = "swimmer" Then
  Me.combo2.RowSource = "SELECT forename FROM [swimmer details]"
Else
  Me.combo2.RowSource = "SELECT stroke FROM stroke1"

 

[pbaldy]

Make sure the rowsource type is table/query.

 

[hrov]

Make sure the rowsource type is table/query.

for combo 2? it wasnt but it is now but i still get the same result

EDIT: I CLOSED THE FORM AND OPENED IT AGAIN AND NOW IT WORKS!

i seriously cant say enough thankyous to you for your patience that you showed to help a beginner

 

[hrov]

for combo 2? it wasnt but it is now but i still get the same result

EDIT: I CLOSED THE FORM AND OPENED IT AGAIN AND NOW IT WORKS!

i seriously cant say enough thankyous to you for your patience that you showed to help a beginner

Paul, now it works with the combo box, how do i go about doing it from the option buttons, (i think they are grouped??? as in they are in the same box and 1 has option value 1, the other 2)

this is my code that currently works:

 Private Sub combo1_AfterUpdate()
If Me.combo1 = "swimmer" Then
  Me.combo2.RowSource = "SELECT forename FROM [swimmer details]"
Else
  Me.combo2.RowSource = "SELECT stroke FROM stroke1"
End If
End Sub

 

[pbaldy]

Did you check posts 6, 8 and 10?

 

[hrov]

Did you check posts 6, 8 and 10?

Firstly where would i put this code?
yes i tried to substitute it in but then i simply just get no results in the combo box.

when you say framename in my case this would be option1=1 right?

this is the code that isnt working:

 Private Sub combo1_AfterUpdate()
If Me.option1 = 1 Then
  Me.combo2.RowSource = "SELECT forename FROM [swimmer details]"
Else
  Me.combo2.RowSource = "SELECT stroke FROM stroke1"
End If
End Sub

 

[pbaldy]

As I mentioned in post 8, you want it in the after update event of the option group (the frame). You still have it in the after update event of the combo. When using an option group, you test the value of the frame, not the individual selections within the frame. The frame will have the value of the selected item. So,

If Me.FrameName = 1 Then