zipcode update city and state

[Linda in Iowa]

A few days ago someone gave me some code so I can enter the zipcode and it
will automatically fill in the city and state. It works great, but I didn't
realize a zipcode can have more than one city. So even though I can select
the correct zip/city it will only enter the first city if more than one does
exist. How do I select the correct zip and city and have it enter the
correct city?
An example is zip 50322 and it is associated with Des Moines, Windsor
Heights, and Urbandale. It will only enter Des Moines in the City field
even though I select one of the others from the drop down list.

code is on the zip field:

Private Sub ZIP_AfterUpdate()
Dim strSql As String
Dim rs As DAO.Recordset

If IsNull(Me.ZIP) Then
Me.City = Null
Me.State = Null
Else
strSql = "select City, state from tblzipcitystate where zip = """ &
Me.ZIP & """;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
If rs.RecordCount > 0 Then
Me.City = rs!City
Me.State = rs!State
End If
rs.Close
End If
Set rs = Nothing
End Sub

Thanks
Linda

 

[Jeff Boyce]

Linda

So, you're saying that if there's only one "hit", you'll take it, but if
there's more than one, you need to see a list so you can pick the correct
one?

Another way to approach this would be to use a combo box that returns unique
combinations of zip + City. That way, you could pick the zip (& City & ...)
that you need.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Linda in Iowa]

On the form it is a combo box and I can see which city I want, so even
though I select zip 50322 with urbandale or windsor heights I still get Des
Moines in the test box for city.

Linda

So, you're saying that if there's only one "hit", you'll take it, but if
there's more than one, you need to see a list so you can pick the correct
one?

Another way to approach this would be to use a combo box that returns unique
combinations of zip + City. That way, you could pick the zip (& City & ...)
that you need.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Jeff Boyce]

Linda

What code are you using in the combo box's AfterUpdate event? If it is the
code you posted originally, that only finds the first instance.

Instead, your AfterUpdate event could include something like:

Me!txtCity = Me!cboZipCode.Column(1)
Me!txtState = Me!cboZipCode.Column(2)

Read over the syntax HELP on the .Column() property, as it is zero-based and
can be confusing.

Regards

Jeff Boyce
<Office/Access MVP>