Dlookup prob

[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

 

[Mike Painter]

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' ")

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 "&"

 

[Beetle]

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)

 

[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

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

Mick