• syntax error in update statement

    Author
    Topic
    #354166

    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.

    Viewing 0 reply threads
    Author
    Replies
    • #519919

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

      • #519987

        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

    Viewing 0 reply threads
    Reply To: syntax error in update statement

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: