Solved Iteration Through Listbox

[dccjr3927]

So my iteration through the listbox works the correct number of times, however, my variables (strProcess, strSKU, and intQty) are not changing with each iteration. They are remaining the same (Row 0) on each record insert.

For i = 0 To Me.lstMultiItem.ListCount - 1
                varItem = Me.lstMultiItem.ItemData(i) 'get next item in list data
                Me.lstMultiItem = varItem     'set listbox to the item
                strSKU = Me.lstMultiItem.Column(0)
                intQty = Me.lstMultiItem.Column(1)
                strProcess = Me.lstMultiItem.Column(2)
                'If CInt(DLookup("SaleableQty", "Inventory", "Inventory.SKU = '" & strSKU & "'")) < CInt(" & intQty & ") Then
                '    MsgBox "There is insufficient item inventory of '" & strSKU & "' to fulfill this order. Please notify manager.", vbOKOnly
                '    Exit Sub
                'End If
                strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                    & strProcess & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & strSKU & "'," & intQty & ",'" & Me.tbxOrderNo.Value & "',#" &                                   dtDateTime & "#)"
                CurrentDb.Execute strRecordWOSQL
                strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty -" & intQty & " WHERE SKU  = '" & strSKU & "';"
                DoCmd.RunSQL strUpdateQtySQL
            Next i

Any help would be greatly appreciated.

 

[Isaac]

You need to utilize the 2nd argument of the listbox's Column reference, which is there for you to provide the row. (i) in your case.

 

[jdraw]

cross posted with responses at https://www.accessforums.net/showthread.php?t=80347

 

[dccjr3927]

Bingo! Perfect! Thank you. I knew I was missing something obvious.

 

[Isaac]

Glad it helped.