Getting fields for form from another table

[PulseTeqUser]

I have a table of orders which has the field Supplier. When I produce the Purchase order as a form based on the Orders table I want to be able to add the address details for the supplier from the Suppliers table. There is a relationship between Supplier in the Suppliers table and Supplier in the Orders table. I have set the Control source on the form to =[Suppliers]![Address1] but I get #Name on the form. What I am missing?

Thanks in advance

 

[missinglinq]

You can't reference a Field in a Table that way, in the Control Source...hence the error.

This kind of thing is usually done using DLookup().

=DLookup("Address1", "Suppliers", "Supplier ='" & [Supplier] & "'")

You could use a Combobox for the Suppliers, with the address Fields included, then assigning them to the appropriate Controls on the Form, using the Combobox AfterUpdate event, but you really should only include the Supplier in the Orders table, simply pulling up the address when needed, in Reports, other Forms, etc.

Linq ;0)>

 

[PulseTeqUser]

The DLookup solution is what I need - thank you very much

 

[missinglinq]

Glad we could help!

Welcome to AWF!

Linq ;0)>