Ted said:
I have a combo box with a list of towns. I also have a text box. When I
choose a town in the combo box I want a post/zip code to automatically
appear in the text box.
I tried the following with vba:
Private Sub cboTown_LostFocus()
if cboTown.text = townName then
txtPO.text = "1234"
end if
End Sub
and:
Private Sub cboTown_OnClick()
if cboTown.text = townName then
txtPO.text = "1234"
End Sub
Neither worked. Does anyone have any ideas?
"Neither worked" is not particularly descriptive of what
happend, but you have several problems. One is your
inappropriate use of the Text property. If you have a VB
background, you need to be aware that the Text property is
only used in a few special situations and that the Value
property is what you should normally use. Since Value is
the default property, you don't even need to mention it.
Another problem is that you're missing the End If statement.
While the LostFocus (and Exit) and even Click events can be
used for this with a combo box, the AfterUpdate event is
more commonly used and is more consistent with other
controls.
Private Sub cboTown_AfterUpdate()
If cboTown = townName then
txtPO = "1234"
End If
End Sub
Now for the obligatory lecture about data structuring ;-)
Because it is so much more complicated to change, you should
not include data values in your code. Far, far better to
place that data in a table.
In this case the table with the town names (used for the
combo box list) should have another field with the town's
zip code (except for towns and cities that have more than
one zip).
With that in place, the combo box's RowSource can be changed
to retrieve the zip field along with the town. This then
allows you to assign the zip to the text box without your
code knowing the town name or the zip code:
Private Sub cboTown_AfterUpdate()
txtPO = cboTown.Columns(1)
End Sub
