default value from another table

[rosebud]

I have (amongst others) two tables called supplier and contacts. Each supplier can have many contacts within it. Most contacts, but not all will have the same address as the supplier so how can I automatically populate the address fields of the contact with supplier address information, so users only need to overttype the information if it differs from the main address. (I have a form with suppliers and a sub form for contacts). I have tried typing =[tblSuppliers]![SupAddress] in the default value properties box of the equivalent Address field in the contact field shown in the sub form, but all I get is #name? so I am obviously not doing something right. The tables are linking as the correct supplier number is shown in the supplierID field in the contacts subform.

I would appreciate any help with this. I do have some limited VBA experience if it is easier to do this in code. Many thanks

 

[rosebud]

Do I need an update query

I have been continuing to try and solve this myself and found another post a couple of years old which mentions an update query. I have adapted this, (I think) to reflect my table and just two of the address fields, but I dont know which event in the sub form I need to enter it into. The code I have amended is as follows with Suppliers being the intial data being input and the contact having the option to use this as the default value. If I am on the wrong track, please let me know as I would be grateful for any advice I can get.

UPDATE tblcontacts INNER JOIN tblsuppliers ON tblcontacts.supplierID = tblsuppliers.supplierID SET tblcontacts.contAddress1 =
[tblsuppliers].[supAddress], tblcontacts.ContAddress2 = [tblsuppliers].[SupAddress1];