Populating a combobox with values dependent on other choices 1

[slames]

Hi, I have a combobox with 6 items in it. If the user picks A, B or C then I want another combobox on the same formto be populated with certain values, in this case January, February, August, September. However if the user picks D or E in the first box I want the second combo box to be populated with a different list i.e March, May November.

I know you can do this using a query but the values I want to return are not stored anywhere. Is there a way of populating a combobox using VBA with a value list, perhaps using an array? - or is it simpler?

Thanks in advance

Steph

 

[HockeyGuy]

If the values that you are filling into the combo boxes is dynamic then you probably should have lookup tables behind these boxes so that you can store and edit the values.

If the values are static (don't change) then you can change the value of the second box depending on the selection in the first box on the AfterUpdate event.

1. Set the RowSource Type of the second combo box to "Value List"
2. Alter the RowSource after the first combo box selection.

Dim strList as String

Select Case Me.FirstComboBox
Case "A", "B", "C"
strList = "Value1;Value2;Value3...."
Case Else
strList = "Value6;Value7;Value8...."
End Select

Me.SecondComboBox.RowSource = strList

3. If the combo box values are table driven then strList could be a SQL string. Example: "SELECT table1.field1 FROM table1 WHERE table1.field2 = somevalue;". You could use the Access query grid to generate the correct SQL syntax for you.

HTH

Rick

ps. I generally have trouble finding these threads so please don't think bad if I don't find you again.

 

[SteveR77]

Steph,


You would need to set up something like this on the AfterUpdate of the first combo box


If (Me.cbo1stOpt=A) OR (Me.cbo1stOpt=B) OR (Me.cbo1stOpt=C) Then

cboValues.RowSourceType = "Value List"
cboValues.RowSource = "January;February; August; September"

Else

cboValues.RowSourceType = "Value List"
cboValues.RowSource = "March; May; November"


End if


HTH,


Steve

 

[slames]

That sounds great thanks guys, much simpler than I thought.
Cheers!

 

[BSman]

If you use a query (from a lookup table) as your source for the second combo box, you need to add to the afterUpdate property of the first combo box:

me.cboCombo2.requery

This forces Access to rerun the query. Since the query should include a criteria based on the value in the first combo box and that value has now changed, the query will now return a different set of values.

If you use a value list (as the second example), I don't think you need to include the requery statement above, but keep it in mind. Access may determine the values for the combo list once when the form is opened (as it does with combo boxes that use a query/table as the source), so if you don't see the values change, add the requery statement.