DLookup in access 2010 help 1

[brendageorge]

I have a subform (Estimates1) in a form (Estimates) that I am applying the DLookup to data source.
=DLookUp("[Price]","[Oakhill Sq]","[Product ID]=[Me].[Product ID]")

I am trying to find the price in table Oakhill Sq that matches the Product ID on my subform.
It returns #Error. What am I doing wrong?

 

[randy700]

How about...

=DLookUp("[Price]","[Oakhill Sq]","[Product ID]=[b][red]" & [/red][/b][Me].[Product ID])

If the Product ID field is not numeric...

=DLookUp("[Price]","[Oakhill Sq]","[Product ID]=[b][red]'" & [/red][/b][Me].[Product ID][b][red] & "'[/red][/b]")

Randy

 

[brendageorge]

Thanks Randy but that generates #Name error.
If I place a product id value as in ='2424P'") it works but I need it to be able to look up the values.

 

[randy700]

and this?

=DLookUp("[Price]","[Oakhill Sq]","[Product ID]='" & [b][red]Forms!YourFormName[/red][/b].[Product ID] & "'")

Randy

 

[dhookom]

brendageorge,
You haven't told us if this is a control source or code. If it is a control source, try:

=DLookUp("[Price]","[Oakhill Sq]","[Product ID]='" & [Product ID] & "'")

Typically the ProductID is a combo box which could contain the price in its Row Source. This makes the expression much simpler:

=YourComboBoxName.Column(x)

Columns are numbered beginning with 0.

Duane
Hook'D on Access
MS Access MVP

 

[brendageorge]

Thanks!!! It is working now. Bless you!

 

[MacroScope]

Your problem is syntax. You're looking up something on the subform, not on the form if I understand you correctly. Try this.

I just copied this straight from a DB I wrote years ago. You'll need to change field and Table/Query names as appropriate.

DLookUp("[Part Number]","[Order Details]","[Part Number]=Forms![Orders]![Orders Subform].Form![Part Number]")

 

[MacroScope]

Your problem is syntax. You're looking up something on the subform, not on the form if I understand you correctly. Try this.

I just copied this straight from a DB I wrote years ago. You'll need to change field and Table/Query names as appropriate.

DLookUp("[Part Number]","[Order Details]","[Part Number]=Forms![Orders]![Orders Subform].Form![Part Number]")