How can I add a field to a MS Access 2002 form which relates to another table?

Question

I have a MS Access 2002 form which basically provides text boxes to manipulate records in a table called Materials. I also have another table called MaterialCosting which has a column MaterialID which links it to the Materials table which also has a column MaterialID. What I am trying to do is to add a text box to this form which reads and writes to a field of MaterialCosting named outerCarton.

Here are my failed attempts:

1. Right click on the form, Choose properties, and in data->Record source, enter an SQL query that joins the two tables (SELECT * FROM Materials AS M INNER JOIN MaterialCosting AS C ON C.MaterialID = M.MaterialID). Then simply put the name of the required column (outerCarton) in the text box's control source.
2. In the text box's control source, put DLookup("[outerCarton]", "[MaterialCosting]", "MaterialCosting.MaterialID = Materials.MaterialID")

Both of these had the same result: the text box would end up with #Name? in it and do nothing.

How can I make this work?

UPDATE

The form's properties The control's properties

Answer 1

I was able to recreate the #Name? you are seeing. It appears its just an SQL statement issue in how you called the data.

To correct it, I had to do the following;

1. For the FORM Record Source place this SQL; SELECT Materials.*, MaterialCosting.* FROM Materials INNER JOIN MaterialCosting ON Materials.MaterialID = MaterialCosting.MaterialID;
2. Now you should be able to select outerCarton from the drop down for Record Source of the text field.
3. Switch to form view and see if you can select records and change the value.

Let me know how that works.