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?


The form's properties Form's properties The control's properties enter image description here

  • You said "add a text box which reads and writes to a column", but you are using a lookup in the control source box. Are you adding just a blank text box which data will be entered into? Or are you trying to add a combo-box for data to be selected from? – CharlieRB Feb 10 '12 at 13:54
  • @CharlieRB I'm trying to add a text box which will display (and allow me to edit) a specific field of MaterialCosting. – Shawn Feb 10 '12 at 19:25
  • What happens when you simply use outerCarton as the Record Source? If the tables are already linked as you say, you should be able to use it without putting a query in the text box. – CharlieRB Feb 10 '12 at 19:51
  • What SQL query did you use in #1? – CharlieRB Feb 10 '12 at 20:06
  • @CharlieRB Simply using outerCarton as the Record Source is what I do in #1. I edited it to add the SQL I use for that as well. It results in the field having #Name? in it. – Shawn Feb 10 '12 at 20:25

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.

  • I don't see the difference between what you're doing and what I'm doing. I was already selecting everything (*). Anyways, I tried your variant and it made no difference. I am indeed able to select outerCartonfrom the drop down when I use an inner join, but I still get #Name?. What does #Name? mean anyways? – Shawn Feb 15 '12 at 19:17
  • #Name? indicates the name used for the control source is not valid. This can be caused by several things. Here is a link to more information about that. – CharlieRB Feb 15 '12 at 19:47
  • By chance, do both tables have a field named outerCarton? – CharlieRB Feb 15 '12 at 19:53
  • No, only MaterialCosting has that field. I'll have a look at that link and ask my employer if I can post a screenshot and then I'll get back to you. – Shawn Feb 15 '12 at 22:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.