How to update a coloumn in access 2007 based on a calculation?

B开发者_运维技巧asically i have a table with one coloumn "stock" and second coloumn "stockissued". supposing stock coloumn has value 40 this means there are 40 items in the store and i want that whenever we type value for stockissued coloumn the previous value of stock is decremented accordingly. Like if out of 40 we write 10 in stock issued field in a form the stock value should decrement from 40 to 30.

Another important thing is that new record in a form for stock field should have the previous decremented value updated everytime.

I need urgent help bcz working on a project!

As you have stated that you are entering the value onto a form, this is actually very easy. You could use the following code on a button click or set it to run when you update the field you typed the value into.

I am working from the assumption that you have the current value and the amount to decrease on the same form.

Dim UpdatedValue as string
UpdatedValue = Me.stock - Me.stockissued
Updatesql = " UPDATE YourTable SET YourTable.Stock=" & (UpdatedValue) & " " & _ 
"WHERE [YourTable]![RecordID] = Me.RecordID "

Docmd.runsql updatesql

Obviously you need to replace the table and field names with your own, also I added in a WHERE criteria that should ensure you dont accidentally update all the records in your table! just the one active on your current form