zipcode update city and state

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

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" 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

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" wrote in message
...
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" 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

Linda,

Attached is a text file that'll do just what you want.

1. Extract the text file to some location on your hard disk.
2. Launch Access, then open any code module.
3. Display the Immediate Window (View | Immediate Window)
4. Enter the following into the Immediate Window, making sure to change the
path to the text file:
LoadFromText acForm, "dlgPostcodes", "c:\Postcodes.frm"

A new form (dialog), called "dlgPostcodes" is created.

5. Create a new table called "tblPostcodes", and add the following fields:
PostcodeID (AutoNumber) Primary Key
Suburb (Text, 50)
State (Text, 3)
Postcode (Text, 4) - Change the size to whatever is appropriate for
your country.

6. Somehow populate the above table.

You can use the dialog in two ways; as a standalone "lookup" form, or as an
object. To use the dialog in the way you want, add the following to the
Declarations section of a relevent calling form:
Private WithEvents Postcodes As Form_dlgPostcodes

Add the following to your calling form's Unload event:
Set Postcodes = Nothing

Then wherever (in the calling form) you want to to use the dialog, use the
following construct:
Set Postcodes = New Form_dlgPostcodes
Call Postcodes.Find(,,,varPostcode)

There are several events that you can use. In the code window, select
"Postcodes" from the Object drop-down. Select whichever event you want from
the Procedure drop-down. This will populate the event stub to which you can
add your code. The events I've exposed are as follows:
Found - which fires when only one postcode was found
MoreFound - which fires when more than one matching postcode was found
NotFound - which fires when no matching postcodes were found
Closed - which fires when the dialog os closed
Cancelled - which fires when the user cancels the dialog
Selected - which fires when the user selects a postcode

There are also fourproperties you can access:
PostcodeID
Suburb
State
Postcode

You can access these using the Postcodes.Suburb construct.

I hope this helps, but be advised, I knocked it up in an hour the other day,
and I haven't had time to debug it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


"Linda in Iowa" 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

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" 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" wrote in message
...
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" 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