Code for subtracting inventory

[Josh]

Does anyone know how I can make Access (SQL) subtract
from an inventory supply. If I enter an order for 2
items, how can it automatically subtract 2 from the
inventory? Thanks.

 

[Ayelet]

Hi,
Try this:
'Code Start
Dim DB As DAO.Database
Dim rsOrder, rsItems As DAO.Recordset

Set DB = CurrentDB
Set rsOrder = DB.OpenRecordset ("Select * From
OrderDetailsTbl Where [OrderID] = " & Me![OrderID])
Set rsItems = DB.OpenRecordset("ItemsTbl")

Do Until rsOrder.EOF
rsItems.FindFirst("[ItemID] = " & rsOrder![ItemID]
If rsItems.NoMatch Then
MsgBox "Couldn't find item - skipping"
Else
rsItems.Edit
rsItems![Amount] = rsItems![Amount] - rsOrder!
[Quantity]
rsItems.Update
End If
rsOrder.MoveNext
Loop
'Code Ends

You could probably do this with an Update query as well,
but you should Never fear a little programming!
Good Luck
Ayelet

 

[Dirk Goldgar]

Does anyone know how I can make Access (SQL) subtract
from an inventory supply. If I enter an order for 2
items, how can it automatically subtract 2 from the
inventory? Thanks.

Along the lines of

CurrentDb.Execute _
"UPDATE Inventory " & _
"SET QtyOnHand = QtyOnHand - " & Me!QtyOrdered & _
" WHERE ItemID=" & Me!ItemOrdered,
dbFailOnError

That's assuming you've performed all necessary checks to ensure that you
have in fact got the required quantity of this particular item in the
inventory.

 

[Guest]

Thank you both very much, My inventory looks okay, I'm
going to try the coding.

-Josh