Lookup Mapping

[Guest]

I have a database that keeps addresses of my students. Among the fields are City, St and Zip. I have the "Lookup" on the Zip field set to be a combo box that links to a Zip Code Table, but I was wondering if I could have City and St filled in automatically when the user picks a zip code.

For example, the user type 90210 in the Zip Code field and Beverly Hills and CA are filled in automatically in City and State. An older program I have called this "Lookup Mapping" and I cannot figure a way to do it in Access.

Any sugesstions would be helpful.

 

[Scott McDaniel]

If a particular zip code is associated with only one city, you could. Or,
if a zip is associated with more than one city you could set one as the
Default. You'd need a table to store this information (Zipcode, City, State,
IsDefault, etc), then in the AfterUpdate event of your combo write code like
this:

Dim rst As New ADODB.Recordset

rst.Open "SELECT City, State, IsDefault FROM tblCities WHERE Zipcode=' " &
SupplyYourZipCodeHere & " ' ", CurrentProject.Connection

If Not(rst.EOF and rst.BOF) Then
Me.CityControl = rst("City")
Me.StateControl = rst("State")
End If

rst.Close
Set rst = Nothing

Of course, if you include the IsDefault functionality, you'll have to change
things a bit to determine if there are more than one record returned and, if
so, which one is set to IsDefault = true.

--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

I have a database that keeps addresses of my students. Among the fields

are City, St and Zip. I have the "Lookup" on the Zip field set to be a
combo box that links to a Zip Code Table, but I was wondering if I could
have City and St filled in automatically when the user picks a zip code.

For example, the user type 90210 in the Zip Code field and Beverly Hills

and CA are filled in automatically in City and State. An older program I
have called this "Lookup Mapping" and I cannot figure a way to do it in
Access.