Loop records and deduct from the oldest date

[Cesar Zapata]

Hi,

I"m building an inventory application. I have a form with subform that
contains

ID RecDate RecQty InStock UnitPrice
100 11/15/06 100 100 2.88
100 11/16/06 200 200 2.98


Now what i'm trying to do is when there is a shipment I need to loop
the records ..starting from the first record and so on. for example if
I ship 50

ID RecDate RecQty InStock UnitPrice
100 11/15/06 100 50 2.88
100 11/16/06 200 200 2.98

but lets say I ship 125
ID RecDate RecQty InStock UnitPrice
100 11/16/06 200 175 2.98

so first record will be gone as the "bin" is empty.

I dont know where to start with VBA....if could help me something to
start up I would really appreciated it. thank you in advance.

 

[John Smith]

Beware air code but something along these lines:

Assumes that something called Shipment contains the number to ship and ID is
the numeric id.

Dim ThisSet as RecordSet, SQL As String
Do While Shipment > 0
SQL = "SELECT InStock, RecDate FROM Inventory" _
& " WHERE ID = " & ID & " AND RecDate =" _
& " (SELECT Min(RecDate) FROM Inventory WHERE ID = " & ID & ")"
Set ThisSet = CurrentDb.OpenRecordset(SQL, db_OpenSnapshot)
With ThisSet
.MoveFirst
If !InStock > Shipment then
SQL = "UPDATE Inventory SET InStock = InStock - " & Shipment
& " WHERE ID = " & ID & " AND RecDate = #" & !RecDate & "#"
CurrentDb.Execute SQL, dbFailOnError
Shipment = 0
Else
Shipment = Shipment - !InStock
SQL = "DELETE FROM Inventory"
& " WHERE ID = " & ID & " AND RecDate = #" & !RecDate & "#"
CurrentDb.Execute SQL, dbFailOnError
End If
.Close
End With
Loop
Set ThisSet = Nothing

Don't forget that Access assumes that all the world uses American style dates.
If you don't then you will need to format RecDate when you build the WHERE
clauses. 'dd/mmm/yyyy' should work provided that there is no time element.

HTH
John
##################################
Don't Print - Save trees

 

[Cesar Zapata]

thank you very much!

Beware air code but something along these lines:

Assumes that something called Shipment contains the number to ship and ID is
the numeric id.

Dim ThisSet as RecordSet, SQL As String
Do While Shipment > 0
SQL = "SELECT InStock, RecDate FROM Inventory" _
& " WHERE ID = " & ID & " AND RecDate =" _
& " (SELECT Min(RecDate) FROM Inventory WHERE ID = " & ID & ")"
Set ThisSet = CurrentDb.OpenRecordset(SQL, db_OpenSnapshot)
With ThisSet
.MoveFirst
If !InStock > Shipment then
SQL = "UPDATE Inventory SET InStock = InStock - " & Shipment
& " WHERE ID = " & ID & " AND RecDate = #" & !RecDate & "#"
CurrentDb.Execute SQL, dbFailOnError
Shipment = 0
Else
Shipment = Shipment - !InStock
SQL = "DELETE FROM Inventory"
& " WHERE ID = " & ID & " AND RecDate = #" & !RecDate & "#"
CurrentDb.Execute SQL, dbFailOnError
End If
.Close
End With
Loop
Set ThisSet = Nothing

Don't forget that Access assumes that all the world uses American style dates.
If you don't then you will need to format RecDate when you build the WHERE
clauses. 'dd/mmm/yyyy' should work provided that there is no time element.

HTH
John
##################################
Don't Print - Save trees