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

    updtate and delete a query

    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.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updtate and delete a query

    I'm a little confused (I'm a lot confused).

    You want to UpDate and then Delete the records of your subForm? Why not just Delete the Records? And have done with it.

    Or do you want to change the records of your subForm, and make corrections to those records.
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updtate and delete a query

    what are you updating in the records
    and why do you need to delete before updating
    JerryC

Posting Permissions

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