Dynamic lookup in SQL sentence

[MrDeej]

Hello guys!

I have some trouble with my code

I have this sentence:

DoCmd.RunSQL "UPDATE [tbl productlines] SET ProductQuantity = 0 WHERE "ordernumber =" & me.ordernumber;"

which i use to update a subform from a mainform by button.

The ting is i need the ProductQuantity to lookup the value in a another table with a ProductNumber (which it finds at the subform it is trying to update) as identifier.

I have tried to use the Dlookup("ProductQuantity", "TBL ProductInformation", "Productnumber=" & me.productnumber) but it fails.

Please help me :)

[MrDeej]

When i use the DLookup("Product quantity", "tbl ProductInformation", "ProductNumber = " & [Form_orderline].Productnumber)
it uses the first ProductQuantity record from Productinformation on all records in [tbl Productline]

How can i make this dlookup use the productnumber in the SQL sentence and not in the subform?

[MMcCarthy]

Your quotes are all wrong. Try this ...

DoCmd.RunSQL "UPDATE [tbl productlines] SET ProductQuantity = 0 WHERE [ordernumber] =" & Me!ordernumber

Mary

[MrDeej]

Your quotes are all wrong. Try this ...

DoCmd.RunSQL "UPDATE [tbl productlines] SET ProductQuantity = 0 WHERE [ordernumber] =" & Me!ordernumber

Mary

Yes. But my problem is this:

ProductQuantity = 0 <---

I need a lookup which uses [tbl productlines].[product number] as criteria for [productquantity]

[MMcCarthy]

Yes. But my problem is this:

ProductQuantity = 0 <---

I need a lookup which uses [tbl productlines].[product number] as criteria for [productquantity]

I don't understand. Can you explain in detail what you want your query to do?

[MrDeej]

I don't understand. Can you explain in detail what you want your query to do?

I have 2 tabels.

[tbl product lines] and [tbl product information]

[tbl product lines] contains order information like which products and the quantity that belonds to a order number (product number, quantity, ordernumber, order quantity, etc etc]

[tbl product information] contains [product number], [product name], [product quantity] (in stock)

The subform is bound to [tbl product lines] and when i push a button on the main form i want all the [tbl product lines].[product quantity] fields in the subform to be updated with the value from [tbl product information].[product quantity]. The criteria is the [product number].

I am sorry about my bad explaining abilities. I am from Norway :)

Please ask me spesific if there are something you dont understand

[Rabbit]

DoCmd.RunSQL "UPDATE [tbl productlines] SET ProductQuantity = " & _ 
   "DLookup(""Product quantity"", ""tbl ProductInformation""," & _
   ""ProductNumber = "" & [Productnumber]) WHERE [ordernumber] =" & _
   Me!ordernumber

I'm a little confused about exactly what you want but try this.

[NeoPa]

Hello guys!

I have some trouble with my code

I have this sentence:

DoCmd.RunSQL "UPDATE [tbl productlines] SET ProductQuantity = 0 WHERE "ordernumber =" & me.ordernumber;"

which i use to update a subform from a mainform by button.

The ting is i need the ProductQuantity to lookup the value in a another table with a ProductNumber (which it finds at the subform it is trying to update) as identifier.

I have tried to use the Dlookup("ProductQuantity", "TBL ProductInformation", "Productnumber=" & me.productnumber) but it fails.

Please help me :)

May I ask why you're using DLookup at all.
You're already within SQL code. Why not link in the info you need using standard SQL?

[MrDeej]

DoCmd.RunSQL "UPDATE [tbl productlines] SET ProductQuantity = " & _ 
   "DLookup(""Product quantity"", ""tbl ProductInformation""," & _
   ""ProductNumber = "" & [Productnumber]) WHERE [ordernumber] =" & _
   Me!ordernumber

I'm a little confused about exactly what you want but try this.

Thank you man!! At last it is working. :) :) :)

[MrDeej]

May I ask why you're using DLookup at all.
You're already within SQL code. Why not link in the info you need using standard SQL?

Good question. I guess you should be able to use SQL code, but i am kindy a n00b at access so i dont know.

The only reference i have is a Wiley, John Walkenback Access 2003 bible as reference. And it is just not a complete bible.

[NeoPa]

You seem happy enough with your answer, so that's good anyway :)

[JConsulting]

Hello guys!

I have some trouble with my code

I have this sentence:

DoCmd.RunSQL "UPDATE [tbl productlines] SET ProductQuantity = 0 WHERE "ordernumber =" & me.ordernumber;"

which i use to update a subform from a mainform by button.

The ting is i need the ProductQuantity to lookup the value in a another table with a ProductNumber (which it finds at the subform it is trying to update) as identifier.

I have tried to use the Dlookup("ProductQuantity", "TBL ProductInformation", "Productnumber=" & me.productnumber) but it fails.

Please help me :)

Glad you got this working, but it seems you could simply link your Main form and Subform using the Master/child relationship to achieve the same goal.
My .02
J

[NeoPa]

My .02
J

Are you waiting for my "Do you need a '$' before your .02?"
To which your reply is on the lines of "Thanks. Very generous of you." :D