Auto-populating City and State Fields from Zip Selection

How to auto-populate City and State fields on a form when the user selects a Zip Code.

Q:  Joe O’Meara wants to auto-populate City and State fields on a form when the user selects a Zip Code.  How can this be done?

 

A:  Here is a procedure that updates city and state from a zip code selected in a combo box bound to a lookup table pre-filled with data.  You can show all three columns in the combo box list (City, State and Zip), so users can see the city and state as they select the zip.  The code writes the City and State data from the 2^nd and 3^rd columns of the combo box’s list, using the zero-based Column(n) syntax.

Private Sub cboZip_AfterUpdate()

 

On Error GoTo ErrorHandler

  

   Dim strCity As String

   Dim strState As String

   Dim strZip As String

  

   strZip = Nz(Me![cboZip])

   strCity = Nz(Me![cboZip].Column(1))

   strState = Nz(Me![cboZip].Column(2))

  

   If strZip <> “” Then

      Me![txtCity] = strCity

      Me![txtState] = strState

   End If

  

ErrorHandlerExit:

   Exit Sub

 

ErrorHandler:

   MsgBox “Error No: ” & Err.Number & “; Description: ” & _

      Err.Description

   Resume ErrorHandlerExit

 

End Sub