473,320 Members | 1,831 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access VBA Looping through Multidimensional Array

2
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Item       Safety_Stock
  3.     011901        917
  4.  
  5.     Item     Location      Lot            QOH
  6.     011901       PR501106    REXI0474    3325
  7.     011901       pp46321b    REXI0474    475
  8.  
Expand|Select|Wrap|Line Numbers
  1.  Public Function InventoryUpdate()
  2.     Dim intTot As Long
  3.     Dim i As Integer
  4.     Dim i2 As Integer
  5.     Dim loopCounter As Integer
  6.  
  7.     'Assign recordsets
  8.  
  9.     'Define recordset to get expected SS data
  10.     Dim rsSS As DAO.Recordset
  11.     Set rsSS = Currentdb.OpenRecordset("SELECT * FROM tbl_ItemxSS")
  12.  
  13.     'Define recordset to get Inventory data
  14.     'Inventory records ID, Site, PL, Item, Desc, Location, Lot, QOH, QtyAlloc, Created, Expire, Status
  15.     Dim rsInv As DAO.Recordset
  16.     Set rsInv = Currentdb.OpenRecordset("SELECT * FROM tbl_Inventory")
  17.  
  18.     ' get rsSS.recordcount and go back to the beginning
  19.     rsSS.MoveLast
  20.     rsSS.MoveFirst
  21.     'Debug.Print rsSS.RecordCount
  22.  
  23.  
  24.     ' Need to update Inventory records returned by subtracting SS 
  25.     Dim RA() As Variant
  26.     ReDim RA(0 To rsSS.RecordCount - 1, 0 To 1)
  27.  
  28.     ' Populate the array with the SS data
  29.     i = 0
  30.     Do Until rsSS.EOF
  31.     'Debug.Print rsSS.Fields(0)
  32.     'Debug.Print rsSS.Fields(1)
  33.         RA(i, 0) = rsSS!Item
  34.         RA(i, 1) = rsSS!Safety_Stock
  35.  
  36.         If rsSS.RecordCount <> 0 Then
  37.             rsSS.MoveNext
  38.             i = i + 1
  39.  
  40.         End If
  41.     Loop
  42.  
  43.     intTot = 0
  44.     loopCounter = 0 ' This will ensure we don't check transactions more than once
  45.  
  46.     Do Until rsInv.EOF
  47.     Debug.Print rsInv.Fields(3)
  48.      Debug.Print rsInv.Fields(7)
  49.  
  50.         If intTot < rsInv!QOH Then                      'if 0 is less than QOH
  51.             For i = loopCounter To UBound(RA)           'Loop through SS array one by one
  52.                 intTot = intTot + RA(i, 1)              'Initialize intTot to be SS Qty
  53.                 If intTot <= rsInv!QOH Then             'If SS Qty <= QOH
  54.                     rsInv.Edit                          'Edit Inventory Table
  55.                     rsInv!QOH = rsInv!QOH - intTot      'Subtract SS from QOH
  56.                     rsInv.Update                        'Update that QOH's with new Qty
  57.                     intTot = 0                          'Reset SS qty to 0 since it was all allocated
  58.                     loopCounter = loopCounter + 1       'increase this so we don't double check a transaction
  59.                     Exit For ' exit loop and move to the next SS Qty
  60.                 End If
  61.             Next i
  62.         Else
  63.             rsInv.Edit
  64.             rsInv!QOH = rsInv!QOH
  65.             rsInv.Update
  66.             intTot = intTot - rsInv!QOH
  67.         End If
  68.         If rsInv.RecordCount <> 0 Then
  69.             rsInv.MoveNext
  70.         End If
  71.     Loop
  72. End Function
  73.  
  74.  
Oct 25 '13 #1
2 2509
shogan
2
Solution provided on another forum:

Expand|Select|Wrap|Line Numbers
  1. Dim rsSS As DAO.Recordset, rsInv As DAO.Recordset
  2. Dim qdf As DAO.QueryDef
  3. Dim intTot As Long, intReduction As Long
  4.  
  5. Set rsSS = CurrentDb.OpenRecordset( _
  6.         "SELECT * FROM [tbl_ItemxSS]", _
  7.         dbOpenSnapshot)
  8. Set qdf = CurrentDb.CreateQueryDef("", _
  9.         "SELECT * FROM [tbl_Inventory] " & _
  10.         "WHERE [Item]=[pCurrentItem] " & _
  11.         "ORDER BY [QOH] DESC")
  12. Do Until rsSS.EOF
  13.     intTot = rsSS!Safety_Stock
  14.     qdf!pCurrentItem = rsSS!Item  ' set query parameter for this iteration
  15.     Set rsInv = qdf.OpenRecordset(dbOpenDynaset)
  16.     Do Until rsInv.EOF
  17.         intReduction = IIf(rsInv!QOH > intTot, intTot, rsInv!QOH)
  18.         rsInv.Edit
  19.         rsInv!QOH = rsInv!QOH - intReduction
  20.         rsInv.Update
  21.         intTot = intTot - intReduction
  22.         If intTot = 0 Then
  23.             Exit Do
  24.         End If
  25.         rsInv.MoveNext
  26.     Loop
  27.     rsInv.Close
  28.     Set rsInv = Nothing
  29.     rsSS.MoveNext
  30. Loop
  31. Set qdf = Nothing
  32. rsSS.Close
  33. Set rsSS = Nothing
Oct 28 '13 #2
topher23
234 Expert 100+
Hurrah for cross-posting! Thanks for posting the answer rather than leaving your post orphaned.
Oct 28 '13 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

10
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...
3
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...
2
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,...
2
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...
4
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...
2
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...
1
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 =...
4
Jezternz
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...
2
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)
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
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...
0
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...
0
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...
1
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)...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.