MS Access Code to Update (add/subtract) value (quantity) on a table/form

[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!

[June7]

Ideally, would not save stock balance to table, calculate when needed.

Review: http://allenbrowne.com/AppInventory.html

[vickyy]

Ideally, would not save stock balance to table, calculate when needed.

Review: http://allenbrowne.com/AppInventory.html

I tried it using this code and it's not working

Private Sub Update_Click()
Update tblStock
Set tblStock.InitialStock = tblStock.InitialStock + tblStock.StockUpdate + Forms!StockUpdate!UpdatedStockField
WHERE tblStock.ModelID = Forms!StockUpdate!ModelID


End Sub

Thanks

[June7]

Where did you find code like that? Can't reference a table directly. Must open a recordset or use domain aggregate function (DSum, DAvg, Count, DLookup, etc).

The point I tried to convey is that should not save the balance, should calculate when needed.