Easy DLookup Issue

[Niki]

Why is this not working?



Brand = DLookup ("[Brand]", "[IngredientsTable], "[FoodName]= " & [FoodName])

Does it not say that the Brand field on my form should equal the Brand field from the IngredientsTable when FoodName from the IngredientsTable equals the FoodName from the drop down box on my form?

I'm missing something obvious....

Thank you~

[pbaldy]

Well, it would help if you clarified what "not working" means, but if the FoodName is a text field the syntax is different:

DLookup Usage Samples

[Niki]

It just keeps giving me the debugger. I tried all 3 methods referring to a form control and I still can't get it to work!

I've attached my DB - if you look on the Form Recipe - not recipes - you'll see I'm trying to get different ingredients listed depending on the recipe. I'd like to be able to select an ingredient from each combo box and populate the text fields to the right depending on the combo box selection. I have used a recordset clone for combo box one to too great effect, and it has populated all my text boxes because, presumably, the control source. I'm trying a DLookup with combo box 2, to see if that would work better for what I'm trying to do.

Thanks for any help!

[pbaldy]

You're comparing an id value against a text value. This works:

Text57 = DLookup("Brand", "IngredientTable", "id=" & Forms!Recipe!myselector1)

as does

Text57 = DLookup("Brand", "IngredientTable", "FoodName='" & Forms!Recipe!myselector1.Column(1) & "'")

But I'd avoid the DLookup entirely and do this:

http://www.baldyweb.com/Autofill.htm

That way the combo has done all the looking up for you. It's more efficient.

[Niki]

Dude. Seriously. I've been working on that for 2 weeks.

Sweet! Thank you so much!!!

[pbaldy]

Happy to help!

[Niki]

Glad to hear it because I have another question - lol.

I've created a text box and asked it to =[Cost Srvg]+[Text197] and it concatenates the feilds instead of summing them.

I've tried =Sum([Cost Srvg]+[Text197]) to no avail - I get #Error.

Thanks in advance!

[pbaldy]

My copy doesn't appear to have a Text197, so I'm not sure what it is. From the sound of it, it contains text data, not numeric, which is why Access will concatenate it.