Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    syntax error in update statement

    Re
    Syntax error in update statement

    I have a form called F1 based on the number of orders issued, and the name of the control is TOrderID. The form is based only on the table orders, therefore i must create a query on the fly with other tables. In the OnClick event i have the following code:

    Dim TOrderID As Control
    Set TOrderID = Forms![F1]![TOrderID]
    Dim db As DAO.Database
    Dim qry As DAO.QueryDef
    'Open the database
    Set db = DBEngine.OpenDatabase("C:BEFrontEnd.mdb")
    'Create query
    Set qry = db.QueryDefs("query1")
    qry.SQL = " SELECT orders.orderid, [order details].cartons, products.stock, products.Productid FROM orders INNER JOIN (products INNER JOIN [order details] ON (products.Productid = [order details].ProductID) AND (products.Productid = [order details].ProductID)) ON orders.orderid = [order details].OrderID;"
    CurrentDb.Execute "UPDATE products.stock = products.[order details]+ products.stock WHERE orderid = " & TOrderID & ";"""

    What i want is to increase the stock with the number of cartons for a given order.The field stock is in the table products, and the field cartons is in the table orderdetails.The field orderid is in the table orders.

    However i cannot construct the update statement in the right way . I cannot increase the stock with the number of the cartons for a given order.Where am i wrong? Is it because my form is based only on the orderid number? But i could construct a query on the fly, so my fault should be somewhere in the Update statement.
    This issue is very importnat for me and i will be really very grateful for any help.

  2. #2
    Lounger
    Join Date
    Feb 2001
    Location
    TX, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntax error in update statement

    CurrentDb.Execute "UPDATE products set stock = [order details]+ stock WHERE orderid = " & TOrderID

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntax error in update statement

    Dear Sir,



    Thank you very much for your answer. I think you have given me the right answer, but somehow my code doesnt work, obvisouly my mistake is somehwere else.What i receive now is the message Too Few Parameters.Expected:2

    Will you agree to send to you my real example ina databsze, compressed with a zip file so
    to save space? i will be very grateful of you would help me because this problem is of very
    important to me

    Best regards

    Slav

  4. #4
    Lounger
    Join Date
    Feb 2001
    Location
    TX, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntax error in update statement

    sure. send it to jbulger@directhitdata.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •