City State Zip??

[laythss]

Does any one know of a place online where you can get the tables (for free I hope) for a city state zip comboboxes, or at least the tables for all cities in the US.

 

[ghudson]

I doubt that you can get a free and legal download of all the US zip codes and the city and states they belong to.

Here are two links from the US post office...

http://www.usps.com/webtools/address.htm
http://www.usps.com/ncsc/addressinfo/addressinfomenu.htm

A google search produced a lot of links to sites that want to sell it all...

http://www.google.com/search?q=free+zip+codes+download&hl=en&lr=&start=0&sa=N

 

[laythss]

allright I bought a database with a table of the information for $9. now I tried to make simple form where when the user enters the zip it will give you the city and state, but my problem was you had to press a Find button, which brought up a find window... Not exactly what I needed.
Here is a link to the way i based my form on
So How do I make the form auto update the other City and state field when a zip code is entered??
The table has three columns: Zip, State and City

 

[sonny]

Maybe this would help. After a user enters a zip, a popup form opens if there is more than one city for the zip. The user only selects the city and the county and city populates the main form. Otherwise, if ther is only 1 city that matches it does its thing without the popup form. I have most of my main form menu driven where the user only types a zip, then select city, then the streets that belong to the city/county.

Private Sub txtZipCode_AfterUpdate()
    If IsNull(Me.[Zip Code]) Then Exit Sub
    Dim db As DAO.Database
    Dim RS As DAO.Recordset
    Set db = CurrentDb()

    Set RS = db.OpenRecordset("SELECT ZipCode, CityName, CountyName FROM tblCounty_CityData WHERE ZipCode =" & Me.txtZipCode & ";")
    'If not found, pop up entry form
    If RS.BOF And RS.EOF Then
        DoCmd.OpenForm "frmAddZip", acNormal, , , acFormAdd, acDialog, Me.txtZipCode
        Me.County.SetFocus
        Me.txtZipCode.SetFocus
    Else
        RS.MoveFirst
        RS.MoveLast
        If RS.RecordCount > 1 Then
            DoCmd.OpenForm "frmChooseCity", , , , , acDialog
        Else
            'If only one matching zip is found, fill City
            Me.txtCITY = RS![CityName]
            Me.County = RS![CountyName]
        End If
    End If
    RS.Close
    Set RS = Nothing
    db.Close
    Set db = Nothing
End Sub

 

[laythss]

sonny, Thank you for the code, I have not tried it yet, but I am sure it works. VB always works. So now I am wondering now if it is even possible at all to do many of the functional form things without using VB?

 

[laythss]

Sonny, I am actually having trouble with the code.

Dim db As DAO.Database

I am getting an error:
'user defined type not defined'

 

[sonny]

You'll need to reference the DAO under tools in vb...

 

[sonny]

I forgot to tell you that theres another popup in the code that allows the user to make a web connection to the US Postal servise to check the zipcode if its not one in the zip table. Basicly it allows the user to use one thats no in the table for the single record but the code never gets added to the table. I can then run a comparison query to see how many records have zips thats not in my table to get a feel for when I need to update it.....

 

[ghudson]

You can check out an old sample db I posted at this thread...
dynamic comboboxes

It does about what you want using combo boxes. Just change around the record sources of the combo boxes so that it searches for values based on the zip code instead of the state.