Dlookup prob

  • Thread starter Thread starter Mick
  • Start date Start date
M

Mick

Hi All

Am using a form/subform and wish to look up a price from an unrelated
entity.

Hence subform has:

[ProductName] which is a text primary key. It needs to look up
[ProductPrice] from tblProduct and return it into [ProductPrice] on
the subform.

If I hard code it as:
______________________-
Me!ProductPrice = DLookup("[ProductPrice]", "tblProduct",
"[ProductName] = 'Kayak' ")
___________________________________
It works fine. However, when I replace the [ProductName] with a combo
and try:
____________________________________________________________-
Dim grabvalue As String

grabvalue = "ProductName = " & "'" & Me!ProductName & "'"
Me!ProductPrice = DLookup("[ProductPrice]", "tblProduct",
"[ProductName] = 'grabvalue' ")

__________________________________________________________-

An error is thrown. An msgbox returns ProuctName = 'Kayak' but doesn't
execute.

How do I get the result of the combo to evaluate to the same as hard
code.

Any help appreciated.

Mick
 
Mick said:
Hi All

Am using a form/subform and wish to look up a price from an unrelated
entity.

Hence subform has:

[ProductName] which is a text primary key. It needs to look up
[ProductPrice] from tblProduct and return it into [ProductPrice] on
the subform.

If I hard code it as:
______________________-
Me!ProductPrice = DLookup("[ProductPrice]", "tblProduct",
"[ProductName] = 'Kayak' ")
___________________________________
It works fine. However, when I replace the [ProductName] with a combo
and try:
____________________________________________________________-
Dim grabvalue As String

grabvalue = "ProductName = " & "'" & Me!ProductName & "'"
Me!ProductPrice = DLookup("[ProductPrice]", "tblProduct",
"[ProductName] = 'grabvalue' ")
Click to expand...

DLookup("[ProductPrice]", "tblProduct", > "[ProductName] = '" & grabvalue &
"' ")

That's single quote, double quote after the equal sign and double quote,
single quote, double quote after the last "&"
 
It should be;

Me!ProductPrice = DLookup("[ProductPrice]", "tblProduct",
"[ProductName] = """ & Me![ProductName] & """")

Note:

1) The reference to the form control Me![ProductName] is outside of
the quotes

2) It's better to use double qoutes rather than single quotes to delimit a
string in case the Product Name were ever to include a single quote.
An easy way to remember how to do the double quotes correctly is to
construct it with single quotes like;

DLookup("[ProductPrice]", "tblProduct",
"[ProductName] = '" & Me![ProductName] & "'")

and then replace each single quote with two double quotes so it
becomes;

DLookup("[ProductPrice]", "tblProduct",
"[ProductName] = """ & Me![ProductName] & """")

(the above tip courtesy of MVP Dave "Klatuu" Hargis)
 
Hi All

Am using a form/subform and wish to look up a price from an unrelated
entity.

Hence subform has:

[ProductName] which is a text primary key. It needs to look up
[ProductPrice] from tblProduct and return it into [ProductPrice] on
the subform.

If I hard code it as:
______________________-
Me!ProductPrice = DLookup("[ProductPrice]", "tblProduct",
"[ProductName] = 'Kayak' ")
___________________________________
It works fine. However, when I replace the [ProductName] with a combo
and try:
____________________________________________________________-
Dim grabvalue As String

grabvalue = "ProductName = " & "'" & Me!ProductName & "'"
Me!ProductPrice = DLookup("[ProductPrice]", "tblProduct",
"[ProductName] = 'grabvalue' ")

__________________________________________________________-

An error is thrown. An msgbox returns ProuctName = 'Kayak' but doesn't
execute.

How do I get the result of the combo to evaluate to the same as hard
code.

Any help appreciated.

Mick
Click to expand...

Thanks for the two suggestions. Although neither throws an error they
don't find the price. I really appreciate your efforts though.

Mick
 
Back
Top