MS Access Code to Update (add/subtract) value (quanity) on a table/form (1 Viewer)

[Vickyy]

I encountered a problem with my MS Access DB and i would

appreciate any help on here please.

I have a 'tblStock' with fields 'ProductID', 'InitialStock', 'Buy', 'Sell'

and 'UpdatedStock'. I also have a form 'StockUpdate' add values

and also add new records to 'tblStock' . If I have value [100] for

IntialStock quantity, Buy [0] and sell [10], UpdatedStock will be [90]

(that's done and fine!). The problem is, I would like to make the

UpdatedStock value [90] to be the NEW InitialStock, so that any BUY

or SELL will keep updating the UpdatedStock and making it the NEW

InitialStock for the next transactions and so on.....but i have been

struggling to find code to implement this.....Any help will be

greatly appreciated.

Thanks in anticipation!

 

[BlueIshDan]

How much wood, would a wood chuck, chuck if a wood chuck could chuck wood? A wood chuck would chuck as much as a wood chuck could, if a wood chuck could chuck wood...

 

[JLCantara]

Hi Vicky!
You most probably have 4 text boxes for your inventory. Add an AfterUpdate event to the buy and sell text boxes then update the InitialStock accordingly. If it is not present, add it as invisible and proceed with its update.

Good luck, JLCantara.

 

[robslob]

I think I'd rather answer BlueIshDan's question

But seriously you are looking at stock control from an idealistic point of view rather than a practial one.

The convention is that you should calculate values from tables rather than store them.

 

[jdraw]

You may want to read this article by Allen Browne.
Good luck.

 

[Vickyy]

Thanks JLCantara, How can I go about that please?

 

[Vickyy]

Hi Vicky!
You most probably have 4 text boxes for your inventory. Add an AfterUpdate event to the buy and sell text boxes then update the InitialStock accordingly. If it is not present, add it as invisible and proceed with its update.

Good luck, JLCantara.

Thanks, That sounds useful but how can I go about it please

 

[JLCantara]

Hello Vicky!

Sorry, I forgot about your post. Here is a way you can deal with you inventory.

Good luck.

 

[Minty]

You shouldn't store a running sum as it becomes a nightmare if you need to edit a mistake or change a value.
How do you determine if you have already added or subtracted the value?
Your current stock total should based on adding and subtracting the total units used / replenished. The link JDraw gave you is a good starting point.

The problem you have encountered is exactly why you shouldn't store a running total...