data entry on form

[Linda in Iowa]

I have a data entry form for entering registrations to an event. I would
like to be able to enter a zipcode and have the city and state be
automatically entered in the appropriate text box. I have a table that has
the zipcode, city, and state each in a field.
Thanks

 

[Allen Browne]

Use the AfterUpdate event procedure of the zipcode to lookup the city and
state, and assign them to the controls.

Example:

Private Sub Zipcode_AfterUpdate()
Dim strSql As String
Dim rs As DAO.Recordset

If IsNull(Me.ZipCode) Then
Me.City = Null 'Want to do this?
Me.State = Null
Else
strSql = "SELECT City, State FROM Table1 WHERE Zipcode = """ &
Me.ZipCode & """;"
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

Note: The example assumes that Zipcode is a Text type field (not a Number
type field.) To see the SQL string you need, you can mock up a query, enter
any zip code in the Criteria row as an example, and switch it to SQL View
(View menu, from query design.)