Jason said:
The structure is rather lengthy but the table lists the following:
Truck #, Part #, Qty Required of that part by that truck, and Qty Available
to Pull from
Most of the trucks require the same parts so I need to figure out how to
cycle through the parts until the Qty Available to Pull goes to 0.
How do you know what Qty Available is? Isn't this calculated on the
fly? Check out Allen Browne's QOH/Inventory solution here:
http://www.allenbrowne.com/AppInventory.html
Also, there's an example of how to handle this type of situation in
Access Cookbook, by Getz and a few others. (see Amazon or the
bookstore).
here's my first try... I'm sure I'm not doing it right, but in the
absence of any other answers...
Option Compare Database
Option Explicit
Public Sub RunAllocation()
'Get list of items to allocate - but only those that have any
quantity in inventory.
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("qtot_CurrentQOH")
'SQL:
'SELECT Inventory.ItemID, Inventory.QOH AS InitialQOH,
[QOH]-nz([SumOfQtyAllocated]) AS QOH_Current
'FROM Inventory LEFT JOIN qtotQtyAllocated ON Inventory.ItemID =
qtotQtyAllocated.ItemID
'WHERE (((Inventory.ItemID) In (SELECT ItemID FROM Delivery WHERE
DateAllocated IS NULL)));
Do Until rs.EOF
Debug.Print "Allocating " & rs.Fields("ItemID") &
rs.Fields("QOH_Current")
Allocate rs.Fields(0)
Debug.Print "Finished " & rs.Fields("ItemID") & ": " &
rs.Fields("QOH_Current")
Debug.Print
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
Public Sub Allocate(ByVal strItem As String)
'--this is probably the most roundabout way of doing this, but I
couldn't figure out how to do
'==it any other way. No, it's all my own code. So any mistakes are
mine.
Dim db As DAO.Database
Dim rsD As DAO.Recordset '--Deliveries with Inventory in first
record.
Dim intQOH As Integer
Dim intAllocated As Integer
Dim intQtyNeeded As Integer
Dim strSQL As String
strSQL = "SELECT Inventory.ItemID, Delivery.OrderDate,
Inventory.QOH, Delivery.TruckID, Delivery.QtyNeeded,
Delivery.QtyAllocated, Delivery.DateAllocated"
strSQL = strSQL & " FROM Delivery INNER JOIN Inventory ON
Delivery.ItemID = Inventory.ItemID"
strSQL = strSQL & " WHERE (((Inventory.ItemID) = '" & strItem & "')
And ((Delivery.DateAllocated) Is Null))"
strSQL = strSQL & " ORDER BY Inventory.ItemID, Delivery.OrderDate;"
Set db = CurrentDb
'clear out old delivery info
'db.Execute "UPDATE Delivery SET QtyAllocated=0, DateAllocated=Null
WHERE DateDelivered IS NULL;", dbFailOnError
Set rsD = db.OpenRecordset(strSQL)
'rsD.MoveFirst
intQOH = rsD.Fields("QOH")
Debug.Print "Initial QOH: " & intQOH
Do Until rsD.EOF Or intQOH = 0
rsD.Edit
'--If there is Qty in inventory to fill the order, fill it,
'--otherwise, don't fill it.
intQtyNeeded = rsD.Fields("QtyNeeded")
If intQOH >= rsD.Fields("QtyNeeded") Then
intAllocated = rsD.Fields("QtyNeeded")
intQOH = intQOH - intAllocated
rsD.Fields("QtyAllocated") = rsD.Fields("QtyNeeded")
Else
intAllocated = intQOH
rsD.Fields("QtyAllocated") = intAllocated
intQOH = 0
End If
rsD.Fields("DateAllocated") = Now
'---this is just feedback for me...
Debug.Print "Needed: " & intQtyNeeded, "Allocated: " &
intAllocated, "QOH: " & intQOH
rsD.Update
rsD.MoveNext
Loop
rsD.Close
Set rsD = Nothing
Set db = Nothing
End Sub
NOTE: Do NOT try this on live data. It WILL screw up your data.
What it does...
1. loops through all the items that have corresponding "orders" and
gets a total on hand.
2. processes each of those items and allocates inventory in date
sequence. Any items that are out of inventory will not get allocated.
(but remainders will). So some orders may be short. (Since the query
for Qty is based on Ordered - Delivered, you should be OK.)
But I'd get a real expert's opinion. I'm just a hack.
Pieter
