Changing combo box list based on another combo box selection

[troy_lee]

I have a continuous form that has one combo box (cbo1) with the
selectable values of "Removal" and "Installation". I would like to
change another combo box (cbo2) value list based on the selection in
cbo1.

For example, when the user selects "Removal" from cbo1 I would like
the value list of cbo2 to be one set of values and when "Installation"
is selected in cbo1 I would like the value list of cbo2 to be a
different set of values.

Is this possible?

Thanks in advance.

Troy Lee

[Salad]

tr******@comcast.net wrote:

I have a continuous form that has one combo box (cbo1) with the
selectable values of "Removal" and "Installation". I would like to
change another combo box (cbo2) value list based on the selection in
cbo1.

For example, when the user selects "Removal" from cbo1 I would like
the value list of cbo2 to be one set of values and when "Installation"
is selected in cbo1 I would like the value list of cbo2 to be a
different set of values.

Is this possible?

Thanks in advance.

Troy Lee

Sometimes cbo2's rowsoure might be
"Select fld from table where id = " & cbo1
In this case, in the AfterUpdate event of cbo1 you'd enter
Me.cbo2.Requery

You might have to change the rowsource in other cases
If me.cbo1 = "Install" then
Me.cbo2.rowsource = "Select fld from table1"
Else
Me.cbo2.rowsource = "Select fld from table2"
Endif

Shark Surfer
http://www.youtube.com/watch?v=6zc79UQj1hQ

[troy_lee]

On May 22, 10:14 am, Salad <[e-mail]:

[e-mail] wrote:

I have a continuous form that has one combo box (cbo1) with the
selectable values of "Removal" and "Installation". I would like to
change another combo box (cbo2) value list based on the selection in
cbo1.

For example, when the user selects "Removal" from cbo1 I would like
the value list of cbo2 to be one set of values and when "Installation"
is selected in cbo1 I would like the value list of cbo2 to be a
different set of values.

Is this possible?

Thanks in advance.

Troy Lee

Sometimes cbo2's rowsoure might be
"Select fld from table where id = " & cbo1
In this case, in the AfterUpdate event of cbo1 you'd enter
Me.cbo2.Requery

You might have to change the rowsource in other cases
If me.cbo1 = "Install" then
Me.cbo2.rowsource = "Select fld from table1"
Else
Me.cbo2.rowsource = "Select fld from table2"
Endif

Shark Surferhttp://www.youtube.com/watch?v=6zc79UQj1hQ

Salad,
Thanks for the input. Why the ambiguity? You say sometimes it might
require a requery and others I may have to use an If...Then statement.
Which method is best in your opinion?

Thanks.

Troy

[CenturionX]

Hello,

When you are changing the values of a combobox, you have to know how
to fill it in

- Inserting a list of values (manualy):
You have to set the RowSourceType first to "Value List", then insert
the values
Me.MyCombo.RowSourceType = "Value List"
Me.MyCombo.RowSource = "Item; Item2; Item3"

- Using a query (automatically):
You have to set the RowSourceType first to "Value List", then insert
the values
Me.MyCombo.RowSourceType = "Table/Query"
Me.MyCombo.RowSource = "SELECT Id FROM Customers" or
Me.MyCombo.RowSource = rsReg where rsReg is a recorsdet

Nice coding.

[CenturionX]

You could use too the Click event of the Combobox_1, so everytime you
pick an item the events trigers and you could profit to change the
combobox_2

Private Sub Combobox1_Click()
if me.Combobox1.value = ucase("install") then

me.Combobox2.rowsource = "Item1; Item2" 'Presetting RowSource
= "Value List"
or
me.Combobox2.rowsource = "SELECT ..." 'Presetting RowSource =
"Table/Query"
or
me.Combobox2.rowsource = rsRecordset 'Presetting RowSource =
"Table/Query"

else
...
endif

:-)

End Sub

[troy_lee]

Nice work guys. I combined the suggestions of you both and it works
beautifully. Thanks for the great input.

BTW, for anyone else interested, this is the code for the solution.

In the After_Update Event of cbo1:

Private Sub cboReworkActivity_AfterUpdate()

If Me.cboReworkActivity = "Removal" Then
Me.txtNewXmtrStatus.RowSourceType = "Value List"
Me.txtNewXmtrStatus.RowSource = "In Cleanroom; Out of
Cleanroom"
Else
Me.txtNewXmtrStatus.RowSourceType = "Value List"
Me.txtNewXmtrStatus.RowSource = "New Xmtr.; Stock Xmtr.;
Original Reworked Xmtr."
End If

End Sub

(Note that txtNewXmtrStatus is really a combo box.)

[paii, Ron]

A usability suggestion would be to disable the 2nd combo box until something
is selected from the 1st.

<tr******@comcast.netwrote in message
news:63**********************************@27g2000h sf.googlegroups.com...

Nice work guys. I combined the suggestions of you both and it works
beautifully. Thanks for the great input.

BTW, for anyone else interested, this is the code for the solution.

In the After_Update Event of cbo1:

Private Sub cboReworkActivity_AfterUpdate()

If Me.cboReworkActivity = "Removal" Then
Me.txtNewXmtrStatus.RowSourceType = "Value List"
Me.txtNewXmtrStatus.RowSource = "In Cleanroom; Out of
Cleanroom"
Else
Me.txtNewXmtrStatus.RowSourceType = "Value List"
Me.txtNewXmtrStatus.RowSource = "New Xmtr.; Stock Xmtr.;
Original Reworked Xmtr."
End If

End Sub

(Note that txtNewXmtrStatus is really a combo box.)