Update a specific record in a table through a fom

[SeanA]

Ok, i'm sure this is simple, but I am stuck. I have a table with a column "QuantityAvailable" and a specific PartID for each row. I have a form with a text box "NewQuantity" and a combobox that selects the specific "PartID". I would like to have a button that replaces the old quantity. So, after the data is typed, the save button is hit and the value is replace. My box is unbound because the text box pulls values from other text boxes and subform. It is a calculated value which is why I am leaving it unbound.



Thanks!

[June7]

From my soapbox - usually, saving aggregate data is a bad design. Calculate aggregate data when needed. Enter transactions of incoming and outgoing, calculate the difference for net.

[SeanA]

This is not a cost. It is a shipment. So, what I am doing is when I make changes on the form, I save a new record with the new quantity and new location for that part. However, theere still may be left over quantity in the old location. I wanted to edit this quantity while retaining all of the old data.

Another though I had was to create 2 new records. One being a copy of the old record with the new associated copy and then the new record with the new location. The problem I had with that was deleting the old record whenever this is done.

[June7]

I didn't think it was cost. Items come in (purchases) to increase stock and items go out (sales) to decrease stock. The difference is the net balance. This has been discussed numerous times in forum threads. What you want can be done but is tricky. Review https://www.accessforums.net/queries...rol-31854.html

[SeanA]

Well, I got it to work. It's not exactly what I wanted but it seems to work. What i did is use two insert statements to add the new records (unless a qty is zero) and deleted the old record. Not the most elegant way to do it but it is working. Thanks, June7!