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.
