decrementing numbers in inventory table

[Guest]

I have an inventory table that I would like to decrement or increment the
quantity in stock field as they are entered into a supplies table using VBA
code. Here's a sample of the inventory table data:

ItemNbr InvID DateRecd QtyRecd QtyInstock Cost
10 67 3/9/1999 57 15 $8.96
10 80 4/28/1999 50 50 $8.96

Would anyone be able to help me with a loop such that if 16 units of ItemNbr
10 were taken, the QtyInStock for InvID 67 would be updated to 0 and the
QtyInStock for InvID 80 would be updated to 49? It should take from the
oldest DateRecd first (FIFO).

Thanks a lot for any help. I am using Access 2007.

 

[George Nicholson]

Here's one approach to decrementing. Incrementing would be a simple append,
wouldn't it?

**Aircode**

Sub DecrementInventory(lngID as Long, lngQty as Long)

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as String

strSQL = "SELECT * FROM InvTable WHERE InvNbr = " & lngID & " ORDER BY
DateRecd"

Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)

Do While Not rs.EOF
If rs!QtyInStock > lngQty Then
'All of lngQty can come out of this record
rs!QtyInStock = rs!QtyInStock - lngQty
lngQty = 0
Else
' only part of lngQty will come from this record (or lngQty =
QtyInStock)
lngQty = lngQty - rs!QtyInStock
rs!QtyInStock = 0
End If

If lngQty = 0 Then
'We're done. Exit Loop
Exit Do
Else
'More to do
rs.MoveNext
End If
Loop

If lngQty >0 Then
MsgBox "Error! Insufficient inventory of #" & lngID & " to cover the
specified requirement. Unmet demand: " & lngQty
End If

Set rs = Nothing
Set db = Nothing

End Sub
**********

HTH

 

[Guest]

Thanks a lot. This is exactly what I was looking for.