I have a main form containing the CustomerID and a subform containing the different orders by customers.
The subform is based only on the table Orders and has the following RecordSource:
SELECT orders.orderid, orders.customerid FROM orders;
Each order contains different products and therefore each order has several records.I want to delete all the records
from a given order and update them.To this end i must build a query based on three tables: orders, orderdetails and produccts.
and build a query of the sort of:
SELECT orders.orderid, products.Productid, products.stock, [order details].cartons
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;
After building the query, i wanrt to click the order that is clicked on above subform, where only the OrderId is shown, and give a command to update and delete all the records within the order being clicked.
CurrentDb.Execute “UPDATE Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID SET Products.stock = [Products].[stock]+[Order Details].[Cartons], WHERE ((([Order Details].OrderID)=” & txtOrderID & “));”
CurrentDb.Execute “DELETE * FROM [Order Details] WHERE orderid = ” & txtOrderID & “;”
However nothing works.
I know that in theory it is possible but all my effors were in vain.
I will be very much obliged to any help.Just to mention that my
database folows very closley the database Nothwind traders shipped with Microsoft.
