Dlookup help

[stampr18]

I have a form and subform that I finally got to work. I now need help with the dlookup for price. I have 3 combo boxes - product, description, and size. Based on those 3 items, I want the price to automatically populate. I think I need to use a dlookup but I'm not sure how because price will be determined on the outcome of all three items.

Thanks,
Nancy

 

[boblarson]

DLookup("TheItemYouWantReturned","TheTableOrQueryYouAreLookingIn","Where [FieldInTableOrQuery]=Me.ComboBox1 And [Field2InTableOrQuery]=Me.ComboBox2 And [Field3InTableOrQuery]=Me.ComboBox3")

And you may have to encapsulate Me.ComboBox1 in single quotes if it is text.

 

[stampr18]

It seems I've been working forever on this and can't get it even close to working. Here is the code I am back to using based on Bob's formula above.

DLookUp("Product Id", "Qry-Product","WHERE[Manufacturer]=Me.'Manufacturer' AND [Product]=Me.'Product' AND [Description]=Me.'Description' AND [Sizes]=Me.'Size'")

I am putting this as an Event Procedure (After Update) in Size.

 

[pbaldy]

Maybe this will help:

http://www.mvps.org/access/general/gen0018.htm

 

[Moniker]

Specify the data type with single quotes:

DLookUp("Product Id", "Qry-Product","[Manufacturer]= '" & Me.Manufacturer & "' AND [Product]='" & Me.Product & "' AND [Description]='" & Me.Description & "' AND [Sizes]='" & Me.Size & "'")

There's no point to putting field names themselves in single quotes. You want the content of the field in single quotes. Also, DLookUp doesn't know what WHERE means, even though it serves about the same purpose as an SQL WHERE statement.

Note this is pretty much the same thing Bob said. I just spelled it out (which we don't like to do too much ).

 

[stampr18]

Thanks for your help. Believe me, I feel like I have tried everything. I copied your code just as you had it and I am still getting this message -->

Compile error: Syntax error

 

[stampr18]

Can somebody send me a copy of a simple database that uses dlookup? I've tried all the examples that have been given to me and still keep getting syntax errors.

I would really appreciate it.

 

[stopher]

Can somebody send me a copy of a simple database that uses dlookup? I've tried all the examples that have been given to me and still keep getting syntax errors.

I would really appreciate it.

I doubt if an example is going to give you any more help than what has been already given here. Perhaps you could post a cut down version of your d/b and we can show the solution.
Stopher