how to decrease value ?

[meenctg]

I have order form. and a item table.
How to i decrease the value when i create order?

I mean in my item table here a stock field. Every item has a stock value. when i input on quantity on order form . it will decease value form item table from stock field.

I have tried with

Private Sub Quantity_AfterUpdate()
CurrentDb.Execute "UPDATE item SET Stock = Stock -Quantity"
End Sub

And

Private Sub Quantity_AfterUpdate()
CurrentDb.Execute "UPDATE item SET Stock = Stock - Me.Quantity"
End Sub

It's not working. It'll show the error
Run time error '3061'
Too few parameter. Expected 1

Any body can give any idea,please?

 

[Galaxiom]

Many inventory system developers elect not to store the number of items in stock but instead calculate it by totaling the purchase and sale transactions for the item. This calculation is done any time the stock quantity is needed.

This system avoids discrepancy between the quantity calculated from transactions and the stored value in the table that results from incrementing and decrementing the stock. Really it is just a fairly strong normalization decision.

The initial stock is entered as a special variant of a purchase transaction. Stock level adjustments similarly.

Where the number of transactions is very large and you don't want to calculate the transactions on every bottle of milk sold in a year, for example, the end of day procedures could calculate an opening stock quantity based on transactions. The current stock is calculated from the opening stock and the transactions on the day.

It is safer than sending separate commands to the database to update a number dozens of times a day. It can be done using transactions or triggers depending on the back end you are using but from a best practices point of view the calculated stock quantity is usually preferred.