updating stock when invoice cancelled

[ZahleServ]

Hello, I've created an invoicing system for my bookstore where everything is working fine. If the invoice includes 3 books it'll update the stock for the book1, book2 and book3 but the problem is when someone cancel an invoice i have a problem fixing the stock back to normal. I've created a sql and a delete query but it only updates the 1st book to the quantity of all the books here's an eg:

invoice1:

book1-> quantity: 2 -> price: $5
book2-> quantity: 1 ....
book3-> quantity: 3 ...


and in the stock table i have:
book1 -> stock: 40
book2 -> stock: 30
book3 -> stock: 20

when i run the query it only updates the book1 to 46 (but i get themsg: you are about to update 3 rows...)
any idea how to solve this issue? thnx

 

[dan-cat]

I've had a nibble at this - check out the two queries qryCancel and qryConfirm which update the stock for each particular item

 

[ZahleServ]

10x, I did the same query but it only updates the 1st book. In your case you are reading from the table (I use the same procedure when adding invoice to substract the stock..., and it works fine) but here, I'm using a query to display the invoice that I want to cancel (in the form frmUpdateInvoice) and the update query is linked to the product name of frmUpdateInvoice and it update to: remainingstock + quantity (in frmUpdateInvoice). I tried many combinations, once the stock updated to the quantity of the 1st book only, another time it updates the 1st book with the total of the quantity... it turns me angry

 

[ZahleServ]

exactly, I noticed that we need seperate queries. I'll do it this way.

concerning the 2nd issue, everything is calculated, on new sales order it calculate the ordered quantity minus the available in stock. and on new purchase from supplier it adds the ordered quantity to the remaining in stock. except for this one (cancelled orders) i'll try it right now and get back to you if i'm still facing a problem. thank you