I need help with my code and/or logic. I need to subtract Safety Stock from an Inventory Table by item by Lot#-Location, with greatest Qty first until the Safety stock is depleted.
I can have multiple Inventory items with different Lot# Location Qty combinations.
The only relationship is Item Number
I think I am going wrong with the loop that subtracts the safety stock and then updates the Inventory table. If there is a better way to do this please let me know.
Any help would be greatly appreciated. -
-
Item Safety_Stock
-
011901 917
-
-
Item Location Lot QOH
-
011901 PR501106 REXI0474 3325
-
011901 pp46321b REXI0474 475
-
-
Public Function InventoryUpdate()
-
Dim intTot As Long
-
Dim i As Integer
-
Dim i2 As Integer
-
Dim loopCounter As Integer
-
-
'Assign recordsets
-
-
'Define recordset to get expected SS data
-
Dim rsSS As DAO.Recordset
-
Set rsSS = Currentdb.OpenRecordset("SELECT * FROM tbl_ItemxSS")
-
-
'Define recordset to get Inventory data
-
'Inventory records ID, Site, PL, Item, Desc, Location, Lot, QOH, QtyAlloc, Created, Expire, Status
-
Dim rsInv As DAO.Recordset
-
Set rsInv = Currentdb.OpenRecordset("SELECT * FROM tbl_Inventory")
-
-
' get rsSS.recordcount and go back to the beginning
-
rsSS.MoveLast
-
rsSS.MoveFirst
-
'Debug.Print rsSS.RecordCount
-
-
-
' Need to update Inventory records returned by subtracting SS
-
Dim RA() As Variant
-
ReDim RA(0 To rsSS.RecordCount - 1, 0 To 1)
-
-
' Populate the array with the SS data
-
i = 0
-
Do Until rsSS.EOF
-
'Debug.Print rsSS.Fields(0)
-
'Debug.Print rsSS.Fields(1)
-
RA(i, 0) = rsSS!Item
-
RA(i, 1) = rsSS!Safety_Stock
-
-
If rsSS.RecordCount <> 0 Then
-
rsSS.MoveNext
-
i = i + 1
-
-
End If
-
Loop
-
-
intTot = 0
-
loopCounter = 0 ' This will ensure we don't check transactions more than once
-
-
Do Until rsInv.EOF
-
Debug.Print rsInv.Fields(3)
-
Debug.Print rsInv.Fields(7)
-
-
If intTot < rsInv!QOH Then 'if 0 is less than QOH
-
For i = loopCounter To UBound(RA) 'Loop through SS array one by one
-
intTot = intTot + RA(i, 1) 'Initialize intTot to be SS Qty
-
If intTot <= rsInv!QOH Then 'If SS Qty <= QOH
-
rsInv.Edit 'Edit Inventory Table
-
rsInv!QOH = rsInv!QOH - intTot 'Subtract SS from QOH
-
rsInv.Update 'Update that QOH's with new Qty
-
intTot = 0 'Reset SS qty to 0 since it was all allocated
-
loopCounter = loopCounter + 1 'increase this so we don't double check a transaction
-
Exit For ' exit loop and move to the next SS Qty
-
End If
-
Next i
-
Else
-
rsInv.Edit
-
rsInv!QOH = rsInv!QOH
-
rsInv.Update
-
intTot = intTot - rsInv!QOH
-
End If
-
If rsInv.RecordCount <> 0 Then
-
rsInv.MoveNext
-
End If
-
Loop
-
End Function
-
-
2 2509
Solution provided on another forum: - Dim rsSS As DAO.Recordset, rsInv As DAO.Recordset
-
Dim qdf As DAO.QueryDef
-
Dim intTot As Long, intReduction As Long
-
-
Set rsSS = CurrentDb.OpenRecordset( _
-
"SELECT * FROM [tbl_ItemxSS]", _
-
dbOpenSnapshot)
-
Set qdf = CurrentDb.CreateQueryDef("", _
-
"SELECT * FROM [tbl_Inventory] " & _
-
"WHERE [Item]=[pCurrentItem] " & _
-
"ORDER BY [QOH] DESC")
-
Do Until rsSS.EOF
-
intTot = rsSS!Safety_Stock
-
qdf!pCurrentItem = rsSS!Item ' set query parameter for this iteration
-
Set rsInv = qdf.OpenRecordset(dbOpenDynaset)
-
Do Until rsInv.EOF
-
intReduction = IIf(rsInv!QOH > intTot, intTot, rsInv!QOH)
-
rsInv.Edit
-
rsInv!QOH = rsInv!QOH - intReduction
-
rsInv.Update
-
intTot = intTot - intReduction
-
If intTot = 0 Then
-
Exit Do
-
End If
-
rsInv.MoveNext
-
Loop
-
rsInv.Close
-
Set rsInv = Nothing
-
rsSS.MoveNext
-
Loop
-
Set qdf = Nothing
-
rsSS.Close
-
Set rsSS = Nothing
Hurrah for cross-posting! Thanks for posting the answer rather than leaving your post orphaned.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ole |
last post by:
Goodday everybody,
i want to create an array that represents data, that has to
be transferred to a pdf doc ( iTextSharp ).
The problem is, that i seem too loose my faith. Namely, ( i have...
|
by: Claire |
last post by:
I have a multidimensional array defined as
private double myArray = new double;
The first column of the array contains X values, the other contains Y values
I have a charting function defined as...
|
by: chris |
last post by:
Hi there,
I created a Multidimensional array of labels
Label lblMultiArray = new Label { {Label3, LblThuTotal},
{Label4,LblFriTotal} };
Now I would like to compare the values in the array,...
|
by: xhunga |
last post by:
I have try a new version of my work.
I have put the sizes of the matrix into the matrix.
A = number of rows
A = number of columns
The first element of the matrix is A
instead of A.
You...
|
by: Gregory.A.Book |
last post by:
I'm working with displaying and manipulating very large image sets. The
program handles anything from 2D images to 4D RGB volumes in a
time-series. I've been using dynamically allocated arrays to...
|
by: phattymatty |
last post by:
I am having trouble getting this loop to do what I would like it to. I need to display information from a multidimensional array in order. The order has already
been sorted in the array using a...
|
by: Snaggy |
last post by:
I have a big multidimensional array and I need to access it at various
levels. The path is stored in a flat array:
I'm doing it like this:
$path = array("people", "men", "tall")
$my_array =...
|
by: Jezternz |
last post by:
First of all I am open to any suggestions and advice. If a javscript multidimensional array is a bad way to do this please say so. I considered XML but I wondered if this would be a bad idea as it...
|
by: ...vagrahb |
last post by:
I am having accessing individual rows from a multidimensional array
pass to a function as reference
CODE:
function Declaration
int Part_Buffer(char (*buffer),int Low, int High)
|
by: Szabolcs Borsanyi |
last post by:
Dear All,
there have been several threads on multidimensional arrays and pointers
to arrays, there is still something I could not fully understand.
(My point here I have raised already, but...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |