Dlookup prob Mick <mick.whyte[ at ]gmail.com> 11/12/2008 12:47:36 AM 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

Re: Dlookup prob "Mike Painter" <mddotpainter[ at ]sbcglobal.net> 11/12/2008 12:53:15 AM Mick wrote: [Quoted Text] > 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 "&"

RE: Dlookup prob Beetle 11/12/2008 1:06:09 AM 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) -- _________ Sean Bailey "Mick" wrote: [Quoted Text] > 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 >

Re: Dlookup prob Mick <mick.whyte[ at ]gmail.com> 11/12/2008 1:43:37 AM On 11 Nov, 19:47, Mick <mick.wh...[ at ]gmail.com> wrote: [Quoted Text] > 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