cascading combo help

[Tara]

I'm struggling with a cascading combo issue and I'm hoping someone can help.

I have a form with 2 combos, one called cboCity the other called cboZip.
cboCity gets it's data from a table called tblZip. The Row Source for cboZip
is qryZip. In the After Update event of this combo, I have the following
code:

me.cboZip.Requery

The requery works, but the zip doesn't automatically show in cboZip. In
other words, the field appears blank. If I hit the dropdown, it shows the
correct zip. I want the zip to automatically show up, rather than the user
having to hit the dropdown and physically choose the zip. Obviously this
can't always happen, since some cities have multiple zip codes, but when
there is only one option, I'd like it to appear.

Thanks in advance!

 

[Dale Fye]

I'm confused, which combo controls the other? If you want the zip combo to
display the city, that is relatively easy (and I wouldn't use a separate
combo for that). If you want the city combo to control the zip, how will you
do that (many cities have more than one zip code)?

If you want zip to control city, then the query I used for the zip combo
would include both the zip and city fields. Then, in the afterupdate event
of the combo, I'd just have some code that displays the city in a textbox.
Something like:

me.txt_City = me.cbo_Zip.column(1)

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Tara]

Thanks for responding Dale. cboCity controls cboZip. It all works fine,
meaning that when I choose a city from cboCity, cboZip is requeried
correctly. The only issue I have is that a value doesn't automatically show
up in the combo box. The user needs to click the arrow and actually choose
the zip, even though in most cases, there is only choice. I want cboZip to
show the zip rather than appearing blank until the users clicks the arrow.

I understand the idea of using the cboZip to control cboCity, but our users
know cities rather than zip codes, so choosing a city rather than a zip is
the easiest option for them.

 

[Dale Fye]

Me.cbo_Zip = Me.cbo_Zip.ItemData(0)

This will pick the first item from cbo_Zip as the automatic selection, but
if there are multiple items, you might want to consider dropping the list
down automatically.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Tara]

I thought about dropping it down automatically...

us there anyway to make that conditional? For instance, only make it
dropdown where there is more than one option?

BTW ~ Thanks for all the help!

 

[Dale Fye]

Sure, you can do it programmatically.

In the AfterUpdate event of your City combo box, try something like this.

Private Sub cboCity_AfterUpdate()

Me.cboZip.Requery

If Me.cboZip.ListCount = 1 Then
Me.cboZip = Me.cboZip.ItemData(0)
Else
Me.cboZip.SetFocus
Me.cboZip.Dropdown
End If

End Sub

You cannot use the dropdown method of the combo box unless it has the focus.

HTH
Dale

 

[Tara]

Thanks Dale! It worked great!

Sure, you can do it programmatically.

In the AfterUpdate event of your City combo box, try something like this.

Private Sub cboCity_AfterUpdate()

Me.cboZip.Requery

If Me.cboZip.ListCount = 1 Then
Me.cboZip = Me.cboZip.ItemData(0)
Else
Me.cboZip.SetFocus
Me.cboZip.Dropdown
End If

End Sub

You cannot use the dropdown method of the combo box unless it has the focus.

HTH
Dale