Need help with If and DLookup

[bgeorge12]

I have a form [order details] that contains a subform [sbforderdetails]. This form is for estimating cost of job. On the main form is a combo box [line] in which the user selects which line of cabinets the estimate is for. On the subform user selects a [product id] and i want the [unitprice]field to lookup the [price] in a table that contains that line's data. I used the following expression to see if this could be doneoakhill cath being one of the lines of cabinets).

=IIF (forms![order details]![line] = [oakhill cath], DLookup ("[price]","[oakhill cath]","[product id]=' " & [product id] * " ' "), " ")

placing this expression in the control source for [unitprice] it returns #name?

Since I have several different lines I can't see writing several IF expressions in the control source so where should I write this, and why does it not work? Any help will be appreciated.

 

[John Big Booty]

Welcome to the forum.

Is [oakhill cath] a field or a table You have treated it as a field in the logical test portion of the Iif statement and then as a Table in the DLookup.

I think also the asterisk in your code should probably be an ampersand.

You might also want to consider implementing a naming protocol for your DB objects, something along the lines of TBL_TableName, QRY_QueryName, FRM_FormName etc. limit yourself to alpha and numeric characters and the underscore, avoid spaces and other special characters, this will make writing code easier and clearer.

 

[bgeorge12]

Sorry for the typo, yes it was suppose to be a &.
Oakhill cath is one of the names that can be selected from the combo box "line". Then I have corresponding tables for each of the lines, therefore oakhill cath is also a table.

 

[John Big Booty]

Where is [product id] coming from in the DLookup?

 

[bgeorge12]

[product id] is from a combo box on the subform. That is where I select what cabinets in need. Such as B18 for base 18". Also [product id] is a field in the table oakhill cath which matches the B18.

Thanks for helping me

 

[John Big Booty]

Try;

=IIF (forms![order details]![line] = [oakhill cath], DLookup ("[price]","[oakhill cath]","[product id]=' " & [forms]![YourMainForm]![YourSubform].[Form]![product id] & " ' "), " ")

 

[bgeorge12]

Thanks I tryed that, but I still get #name? error.

 

[John Big Booty]

Can you post a copy of your DB?