Home » Category » Microsoft Access

Microsoft Access: zipcode update city and state

204| Fri, 06 Jun 2008 12:57:00 GMT| lindainiowa| Comments (4)
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

Keywords & Tags: zipcode, update, city, state, microsoft, access

URL: http://database.itags.org/ms-access-database/207241/
 
«« Prev - Next »» 4 helpful answers below.
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" <tandembent...mchsi.com> wrote in message
news:N4qlf.383953$084.196244...attbi_s22...
>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
>

jeffboyce | Fri, 06 Jun 2008 12:59:00 GMT |

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.

"Jeff Boyce" <nonsense...nonsense.com> wrote in message
news:OUJXLgs%23FHA.532...TK2MSFTNGP15.phx.gbl...
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" <tandembent...mchsi.com> wrote in message
news:N4qlf.383953$084.196244...attbi_s22...
>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
>

lindainiowa | Fri, 06 Jun 2008 13:00:00 GMT |

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>

"Linda in Iowa" <tandembent...mchsi.com> wrote in message
news:N5slf.602234$x96.134882...attbi_s72...
> 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.
> "Jeff Boyce" <nonsense...nonsense.com> wrote in message
> news:OUJXLgs%23FHA.532...TK2MSFTNGP15.phx.gbl...
> 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" <tandembent...mchsi.com> wrote in message
> news:N4qlf.383953$084.196244...attbi_s22...
>

jeffboyce | Fri, 06 Jun 2008 13:01:00 GMT |

Total Zip Code Database from FMS is the USPS zip code database with a sample Access database that has a form which handles zip codes with multiple cities.  It shows a combo box list with the primary city and each of the additional ones.

The demo which shows it is free.
http://www.fmsinc.com/MicrosoftAccess/ZipCodeDatabase.html

guest | Mon, 07 Jun 2010 21:12:00 GMT |

Microsoft Access Hot Answers

Microsoft Access New questions

Microsoft Access Related Categories