Displaya Value in a Field on a Form based on Value in another Table

[d8a]

I'm new to Access and got this database project, Which is to use Access 2003 as the frontend for data which will be stored on an SQL server.

I have an Addr Type Code Table for data such as, mailing, street, or shipping address. I also have a Customer Address Table with a field for Addr Type Code and Addr Type Code Desc.

When someone selects an Addr Type Code from the list box on the Form which corresponds to the Customer Address Table I want the Addr Type Code Desc field, on the Form to fill-in automatically. I have a one to many relationship between the Addr Type Code Table and the Customer Address Table.

This seems like something which sould be super simple and I will be repeating it many times for many of the "code" Tables I've setup; and thus far I haven't figured out how to do it, nor have I found any great tutorial descriptions on how to do it using a Query, although I have been guided to the point of using a Query to make it happen. Unfortunately every sample Query I've found is from an MDB not an ADP and I'm needing help with creating the query, step-by-step, if a query is the best way to accomplish this.

 

[LaurieHamlin]

If I correctly understand what you're doing, a recordset should do it.
Dim db As Database
Dim rd As Recordset
Dim ATC as string 'for address type code
Set db = CurrentDb()
ATC = [name of form list box they'll be choosing from].value
Set rd = db.OpenRecordset("select * from [Customer Address Table] where [Customer Address Table].

 = ' " &         _ATC & " ' ", dbOpenDynaset)    'I added spaces so you can see the different quote marks
[name of form field you want description to appear in].value = rd.fields("name of field in Customer Address Table")
Hope that helps.