Re: zipcode update city and state

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@xxxxxxxxx> wrote in message
news:N4qlf.383953$084.196244@xxxxxxxxxxxx
>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
>


.

Relevant Pages

  • Re: synchronize combo boxes
    ... should be for cbozip, i have changed so many times. ... Private Sub City_AfterUpdate ... " ORDER BY City" ... Zip is equal to the value of cboCity... ...
    (microsoft.public.access.gettingstarted)
  • Re: No results on a multiple parameter search
    ... For example, one column is "City". ... If I were to search for Austin, ... Private Sub cmdSearch_Click ... Dim strSql As String ...
    (microsoft.public.access.forms)
  • Re: Zip Code Lookup
    ... Center for Health Program Development and Management ... When the user enters the zip code into the form I want the city and state to autofill. ... Private Sub Zip_Code_AfterUpdate ... Dim rsCurr As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • No results on a multiple parameter search
    ... For example, one column is "City". ... If I were to search for Austin, ... Private Sub cmdSearch_Click ... Dim strSql As String ...
    (microsoft.public.access.forms)
  • Re: data entry on form
    ... Private Sub Zipcode_AfterUpdate ... Dim strSql As String ... The example assumes that Zipcode is a Text type field (not a Number ... >like to be able to enter a zipcode and have the city and state be ...
    (microsoft.public.access.forms)