Combo box choices based on another combo box

[Tara]

I know I've seen this addressed many times before, but I
can't seem to find what I'm looking for in past
messages. I have a form that has several combo boxes.
Combo1 is a choice between County and Hospital. If the
user chooses county in this combo, then I would like
Combo2 to display all the counties my agency services.
If the user chooses hospital in Combo1, I would like
Combo2 to list the area hospitals that we service instead
of the counties. Hope someone can help me with this!

Thanks

 

[Gerald Stanley]

In the combo1 AfterUpdate eventhandler, place code similar
to the following

If combo1 = "County" Then
combo2.RowSource = "SELECT countyColumnName FROM
CountyTable"
Else
combo2.RowSource = "SELECT hospitalColumnName FROM
HospitalTable"
End If

You will have to change column and table names to suit your
app.

Hope This Helps
Gerald Stanley MCSD

 

[Cheryl Fischer]

You could try something like the following in the AfterUpdate event of your
first combo box:

If Me!Combo1 = "County" then
Me!Combo2.RowSource = "Select County from tblCounty order by County"
else
Me!Combo2.RowSource = "Select Hospital from tblHospital order by
Hospital"
End If
Me!Combo2.Requery

 

[fredg]

I know I've seen this addressed many times before, but I
can't seem to find what I'm looking for in past
messages. I have a form that has several combo boxes.
Combo1 is a choice between County and Hospital. If the
user chooses county in this combo, then I would like
Combo2 to display all the counties my agency services.
If the user chooses hospital in Combo1, I would like
Combo2 to list the area hospitals that we service instead
of the counties. Hope someone can help me with this!

Thanks

I'll assume that both the counties and the hospital names reside in
the same table, and one field in the table designates whether the
[NameField] is a county or hospital.

Leave the RowSource property of Combo2 blank.

Code the AfterUpdate event of Combo1:

Combo2.RowSource = "Select YourTable.[NameField] from YourTable Where
YourTable.[Type] = '" & Me!Combo1 & "';"

Change YourTable to the actual table name.
Change [Type] to whatever the name of the field is in the table that
designates whether it is County or Hospital.
The above assumes [Type] is a Text datatype field and that the Combo1
bound column is also Text, not a number.

If the Bound column is a number, then use:

Combo2.RowSource = "Select YourTable.[NameField] from YourTable Where
YourTable.[Type] = " & Me!Combo1 & ";"

 

[Tara]

Thanks for the help. It worked great! Now...How can I
get Combo2 to come up blank when it is opened? Right
now, it shows the last choice the user made. Also, If I
first choose county in Combo1, then choose hospital,
Combo2 still shows the choice I made for county until I
hit the dropdown arrow. I would like it to show up empty
with each new record. Thanks for any more help. I
appreciate it!

Tara

 

[Gerald Stanley]

In the Form's Load eventhandler, put combo2.RowSource = ""

Hope This Helps
Gerald Stanley MCSD