A few years ago, before "finding" Richard and becomming a student, I wrote an Access POS and store management app for our retail golf shop. Since then, I have been upgrading a lot of my "cheesy" code based on all the cool stuff I have learned from Richard's videos and seminars.  

However, I am struggling with a proper Inventory system.  My existing method of tracking inventory is "double-cheesy" and is taking up a lot of space unnecessarily. From Richard's Developer lessons and his SQL Seminars I have managed to figure out the Receiving part of the inventory system using a simple UPDATE SQL statement that adds the qty received to the QtyOnHand field in the Product table. My problem is when the product is sold or disposed of.

When a product is sold, the sale is handled by a Transaction form and TransactionDetail subform which store the transaction base transaction information in tblTransaction and the sales detail in the related tlbTransDetail.

At the point when the transaction is saved (button on main form), I am trying to loop through the recordset of the subform and update the Quantity field in the Product table for each item.  I've been wrestling with this for the last 10 days unsuccessfully.  I can't seem to translate the lessons I have found to my particular situation.

I hope someone can point me in the right direction.  Much appreciated.  Thanks