automatically filling a form field with zipcode based on city name

[Guest]

When a user enters a city in the "city" field of my form, I want the
"zipcode" field to automatically be filled in (and also saved in the zipcode
field of the underlying table).

I already have a separate table named "Zipcodes" with the cities in the
first column and the corresponding zipcodes in the second.

not sure if and how to use Dlookup, or make some type of macro that runs
after the City field of my form is updated, or what..

Thanks in advance for the help.
Steve

 

[fredg]

When a user enters a city in the "city" field of my form, I want the
"zipcode" field to automatically be filled in (and also saved in the zipcode
field of the underlying table).

I already have a separate table named "Zipcodes" with the cities in the
first column and the corresponding zipcodes in the second.

not sure if and how to use Dlookup, or make some type of macro that runs
after the City field of my form is updated, or what..

Thanks in advance for the help.
Steve

What Zip code would you like to have entered when the user enters New
York, or Chicago, or Los Angeles? Each of which has many.

 

[Guest]

this is not relevant as my geographical area is the suburbs where one city
only has one zip code

 

[John Vinson]

When a user enters a city in the "city" field of my form, I want the
"zipcode" field to automatically be filled in (and also saved in the zipcode
field of the underlying table).

How do you intend to handle the case of cities (and there are many!)
with two, or twenty, different zipcodes?

I already have a separate table named "Zipcodes" with the cities in the
first column and the corresponding zipcodes in the second.

not sure if and how to use Dlookup, or make some type of macro that runs
after the City field of my form is updated, or what..

Simplest would be to use the AfterUpdate event of the Cities combo box
(by all means use a combo, not a textbox, to save effort and avoid
misspellings); click the ... icon and choose Code Builder. I'd suggest
allowing for multiple zips, or for unknown cities, by using a
Recordset. Something like

Private Sub cboCity_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
' initialize the zipcode combo box cboZip
Me.cboZip.RowSource = "SELECT Zip, City FROM [Zipcodes] ORDER BY Zip;"
If Not IsNull(Me.cboCity) Then
strSQL = "SELECT Zip, City FROM [Zipcodes] WHERE [City] = " _
& Chr(34) & Me.cboCity.Column(0) & Chr(34) _
& " ORDER BY Zip;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Select Case rs.RecordCount
Case 0 ' No zips found for this city
Msgbox "This city not found, please enter zip manually"
Case 1 ' only one zip found
Me.cboZip = Me.cboCity.Column(1)
Case Else ' more than one zip found
MsgBox "Please select correct zip code"
Me.cboZip.RowSource = strSQL
Me.cboZip.SetFocus
End Select
End If
End Sub

John W. Vinson[MVP]