Using Dlookup in Subform

[dnjtdjq]

I'm a beginner in Access and I'm not sure if what I want to do is correct or possible to do in Access but I will give it a go.

Im trying to make a recipe template using Access Form and I have three tables I first made.

Combined Table, Ingredients Table and Recipe Table.

In Ingredients Table, I have 3 fields which are Ingredients ID, Ingredient and price.

In Combined Table, I have Recipe ID, Ingredients, Price, Amount and Total

When I made a Form, I have put Recipe Table as a main form and Combined table as a subform.

Then, in subform I have deleted Recipe ID.

What I want to do is the price field in subform to be auotmatically fileld if when I type Ingredients in subform ingredients field.

The dlookup formula I used in the control source of a combo box is =DLookUp("Price","Ingredients Table","Ingredient=" & [Ingredient Combo])

And no matter how I change the formula, the end result is #ERROR.

I have attached the screenshot of what I did so hopefully this would help.

Thanks​

[Bob Fitz]

IMHO it would be better to include the price field as a column in the Ingredients combo box and reference that column in an expression used as the Control Source property of the Price textbox control.

[CJ_London]

I agree with Bob - but a reason your dlookup fails is because you have spaces in your table name - spaces mean you have to surround the name with square brackets -"[Ingredients Table]".

[Bob Fitz]

I agree with Bob - but a reason your dlookup fails is because you have spaces in your table name - spaces mean you have to surround the name with square brackets -"[Ingredients Table]".

Might also be because Ingredients combo box is bound to Ingredients ID field rather than the Ingredient field.

[dnjtdjq]

IMHO it would be better to include the price field as a column in the Ingredients combo box and reference that column in an expression used as the Control Source property of the Price textbox control.

Thanks Bob,
I'm quite lost on how I can do what you say.
Could you please explain a bit more so I can follow what you just said.

Thank you

[Bob Fitz]

Can you show us the sql statement used in combo box and tell us the name of the price field in the ingredients table

[Micron]

cross posted here
https://www.mrexcel.com/forum/micros...p-subform.html