none
Access VBA Help Needed Looping Through Multidimensional Array RRS feed

  • Question

  • My goal with the code below is 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 of the same item with different Lot# Location Qty combinations in the Inventory table.

    The only relationship between the SS table and inventory table 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. I suspect I need to somehow make sure the Item from the Inventory table is referenceing the Item from the SS array populated from the SS table.

    Any help would be greatly appreciated.

    Safety Stock Table Example:

    Item       Safety_Stock

    011901        917

    Inventory Table Example

    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

    Saturday, October 26, 2013 4:09 PM