Code for subtracting inventory

  • Thread starter Thread starter Josh
  • Start date Start date
J

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.
 
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
 
Josh said:
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.
Click to expand...

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.
 
Back
Top