DLookUp Function Question 2

[starflt1701]

Can anyone help me with the DLookUp function? I want to display, in a form, a value in a field, from a foreign table or query, based on the entered value of another field (control) on the form. My Access manual, and the help function in Access do not agree on the proper formatting of the expression. However, I have tried both, and neither work. If there is a better way other than DLookUp to accomplish my objective, can you please recommend in detail?

Thanks,

 

[JerryDennison]

The syntax for DLookUp() is:

DLookUp("[FieldToLookUp]", "TableOrQueryName", "[CriteriaField] = " & Forms!FormName!Criteria)

You can also try simply adding the table or query that has the value you wish to lookup to the form's underlying recordset and link it via the lookup field. The only problem with this is you may end up with an non-updatable recordset.

 

[LonnieJohnson]

If the criteria field is a string then you will need to add apostropes...

DLookUp "[FieldToLookUp]", "TableOrQueryName", "CriteriaField]" = '" & Forms!FormName!Criteria & "'&quot
ljprodev@yahoo.com
ProDev
MS Access Applications

 

[LonnieJohnson]

If the criteria field is a string then you will need to add apostropes...

DLookUp "[FieldToLookUp]", "TableOrQueryName", "CriteriaField] = '" & Forms!FormName!Criteria & "'&quot

Sorry I had one extra quotation mark in the expression. ljprodev@yahoo.com
ProDev
MS Access Applications

 

[starflt1701]

Ok. Thanks for the help with the syntax. DLookUp is now working great. However, all I can do at this point is look up the related field. I want to be able to save the looked up information to a new table via the form. I have 2 primary fields: Job No. and Part Code. I want to look up the part code associated with a job number I key into my form, and save both fields to another table (along with other keyed information). How do I do that?