automatically populate a field on a form

[Guest]

How do I have Access display the value from an existing table, depending on
the input in another field?

Example: if the user enters a zip code (37075), I would like access to
lookup the city and state information from another table and display it in
the same form, when the user exits the zip code field.

Thanks in advance for your help.

Jeff

 

[Allen Browne]

Answered whereever else you posted this question previously.

 

[Guest]

This is the first time I have posted this question. I have only discovered
this forum in the last 24 hours. I will look through the previous posts of
other users. Any tips for where to look?

 

[Allen Browne]

Okay, "Linda in Iowa" asked the same question earlier today in this group.
Here is my reply to her:

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.)