By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,441 Members | 998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,441 IT Pros & Developers. It's quick & easy.

Dynamic lookup in SQL sentence

100+
P: 157
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 11 '07 #1
Share this Question
Share on Google+
12 Replies


100+
P: 157
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?
May 11 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Your quotes are all wrong. Try this ...

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "UPDATE [tbl productlines] SET ProductQuantity = 0 WHERE [ordernumber] =" & Me!ordernumber
Mary
May 12 '07 #3

100+
P: 157
Your quotes are all wrong. Try this ...

Expand|Select|Wrap|Line Numbers
  1. 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]
May 14 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
May 14 '07 #5

100+
P: 157
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
May 14 '07 #6

Rabbit
Expert Mod 10K+
P: 12,365
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "UPDATE [tbl productlines] SET ProductQuantity = " & _ 
  2.    "DLookup(""Product quantity"", ""tbl ProductInformation""," & _
  3.    ""ProductNumber = "" & [Productnumber]) WHERE [ordernumber] =" & _
  4.    Me!ordernumber
I'm a little confused about exactly what you want but try this.
May 15 '07 #7

NeoPa
Expert Mod 15k+
P: 31,475
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?
May 15 '07 #8

100+
P: 157
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "UPDATE [tbl productlines] SET ProductQuantity = " & _ 
  2.    "DLookup(""Product quantity"", ""tbl ProductInformation""," & _
  3.    ""ProductNumber = "" & [Productnumber]) WHERE [ordernumber] =" & _
  4.    Me!ordernumber
I'm a little confused about exactly what you want but try this.
Thank you man!! At last it is working. :) :) :)
May 15 '07 #9

100+
P: 157
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.
May 15 '07 #10

NeoPa
Expert Mod 15k+
P: 31,475
You seem happy enough with your answer, so that's good anyway :)
May 15 '07 #11

JConsulting
Expert 100+
P: 603
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
May 15 '07 #12

NeoPa
Expert Mod 15k+
P: 31,475
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
May 15 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.