Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    Iterate thru Listbox for INSERT SQL Statments

    Good Morning:



    On a form I have a 3 column listbox that is populated by text and combo box selections. The listbox holds SKU, Qty, and Process for each item on a multi-item order. I need to find a way to build a loop for my INSERT command, each time using a row from the list box.

    This is my INSERT statement for single item orders

    Code:
    "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                    '    & Me.cbxProcess.Column(0) & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & Me.cbxSKU.Value & "'," & Me.tbxSKUQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
    So, first, I would like to know how to loop through the list box, either getting the 3 values or selecting the row (or next row). Then how to call each column value for that correct row in the SQL statement instead of from the cbxProcess, cbxSKU, and tbxSKUQty.

    I hope that this makes sense to someone other than me.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    cycle thru listbox:


    Code:
    Dim i As Integer
    dim vItm
    
    
    For i = 0 To lstBox.ListCount - 1
       vItm= lstBox.ItemData(i)  'get next item in list data
       lstBox = vItm     'set listbox to the item
     
         'now get values from field columns
       vName = lstBox.column(0)  'in vb, columns start with zero
       vCity = lstBox.column(1)
    
        'do stuff with it here
    Next

  3. #3
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    I apologize for the delay in getting back. So, I made the changes above and this is the entire sub where it is used. However, I keep getting the compile error "Next without For" on the very first use. I get this error even though I am testing with a single item (ie., Me.tglMultiItem is False). Obviously I have both For and Next in each part. Any insights would be greatly appreciated.

    Code:
    Private Sub cmdCompleteAnother_Click()
    Dim strRecordWOSQL As String
    Dim strRecordLarqSQL
    Dim strUpdateQtySQL As String
    Dim dtDateTime As Date
    Dim strSerialList As String
    Dim nIndex As Integer
    Dim i As Integer
    Dim varItem As Variant
    Dim varSKU, varQty, varProcess As Variant
    
    dtDateTime = Now
    strRecordWOSQL = ""
    strUpdateQtySQL = ""
    strRecordLarqSQL = ""
    
    If Me.cbxSKU.Value = "CE-HUMIDOR-CAR-1" Then
        MsgBox ("Visually verify that there is a wooden tray, bottle of solution, and 1 wooden hydrostick in the humidor. If not, add missing items.")
    Else
    End If
    
    If Me.cbxContractCo.Value = "Contract - Nomi-Sibs" Then
        MsgBox ("Please insure that product is clean and all lasering residue has been removed.")
    Else
    End If
    
    If Me.cbxSKU.Value = "CE-HUMIDOR-1-MAG-DIG" Then
        MsgBox ("Remove analog hygrometer from inside and replace with round digital hygrometer (CE-HYGRO-DG).")
        strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty - 1 WHERE SKU  = 'CE-HYGRO-DG';"
        DoCmd.RunSQL strUpdateQtySQL
    Else
    End If
     
    If DCount("OrderNo", "WOTracking", "OrderNo='" & Me.tbxOrderNo & "'") >= 1 Then
        If MsgBox("This order already exists. Create an additional entry?", vbOKCancel, "DUPLICATE RECORD") = vbCancel Then
            Exit Sub
        Else
        End If
    Else
    End If
    
    If Me.tglMultiItem.Value = Yes Then
        'Multi_Item Order
        If Me.cbxContractCo = "Other" Then
            For i = 0 To lstMultiItem.ListCount - 1
               varItem = lstMultiItem.ItemData(i) 'get next item in list data
               lstBox = varItem     'set listbox to the item
            'Assign Values
               varSKU = lstBox.Column(0)  'in vb, columns start with zero
               varQty = lstBox.Column(1)
               varProcess = lstBox.Column(2)
            'Insert WO Record
               strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                    & varProcess & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & varSKU & "'," & varQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
               CurrentDb.Execute strRecordSQL
            Next
        Else
            For i = 0 To lstMultiItem.ListCount - 1
                varItem = lstMultiItem.ItemData(i) 'get next item in list data
                lstBox = varItem     'set listbox to the item
            'Assign Values
                varSKU = lstBox.Column(0)  'in vb, columns start with zero
                varQty = lstBox.Column(1)
                If CInt(DLookup("SaleableQty", "Inventory", "Inventory.SKU = '" & varSKU & "'")) < CInt(varQty) Then
                    MsgBox "There is insufficient item inventory to fulfill this order. Please notify administrator.", vbOKOnly
                varProcess = lstBox.Column(2)
            'Insert WO Record and Update Inventory
               strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                    & varProcess & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & varSKU & "'," & varQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
               CurrentDb.Execute strRecordSQL
               strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty -" & Me.tbxSKUQty.Value & " WHERE SKU  = '" & Me.cbxSKU & "';"
               DoCmd.RunSQL strUpdateQtySQL
            Next
                'Insert Serial Number Data for Larq
                If Me.cbxContractCo = "Contract - Larq" Then
                    For nIndex = 0 To lstSerialNos.ListCount - 1
                    strSerialList = strSerialList & lstSerialNos.ItemData(nIndex) & ","
                    Next
                    strSerialList = Left$(strSerialList, Len(sList) - 1)
                    strRecordLarqSQL = "INSERT INTO LarqData (LarqOrder, LarqSerial) VALUES (Me.tbxOrderNo.Value , strSerialList)"
                    CurrentDb.Execute strRecordLarqSQL
                Else
                    Exit Sub
                End If
        End If
    Else
    'Single Item Order
        If Me.cbxContractCo <> "Contract - Other" Then
            strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                & Me.cbxProcess.Column(0) & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & Me.cbxSKU.Value & "'," & Me.tbxSKUQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
            CurrentDb.Execute strRecordWOSQL
        Else
            strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                & Me.cbxProcess.Column(0) & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & Me.cbxSKU.Value & "'," & Me.tbxSKUQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
            CurrentDb.Execute strRecordWOSQL
            strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty -" & Me.tbxSKUQty.Value & " WHERE SKU  = '" & Me.cbxSKU & "';"
            DoCmd.RunSQL strUpdateQtySQL
            If Me.cbxContractCo = "Contract = Larq" Then
                strRecordLarqSQL = "INSERT INTO LarqData (LarqOrder, LarqSerial) VALUES ('" & Me.tbxOrderNo.Value & "','" & Me.tbxSerialNo.Value & "')"
                CurrentDb.Execute strRecordLarqSQL
            Else
            End If
        End If
    End If
    'Clears form for more entries
        Me.tbxOrderNo = ""
        Me.cbxSKU = ""
        Me.cbxSKU = ""
        Me.cbxProcess.Value = ""
        Me.tbxSKUQty.Value = "1"
        Me.tbxSerialNo.Value = ""
        Me.tbxOrderNo.SetFocus
    End Sub

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If CInt(DLookup("SaleableQty", "Inventory", "Inventory.SKU = '" & varSKU & "'")) < CInt(varQty) Then

    Does not have companion End If

    DLookup() returns Null if match not found and CInt will error.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    OK. So I tried to clean up the code a little, eliminate some of the nested IF statements. Here is the revision. I keep getting a "Next without For" error (red). Any suggestions? I would also like to know if there is a better way to handle this multi-item portion. I have to check each item in the list box to insure that sufficient inventory is present before running in INSERT and UPDATE portions. My code seems cumbersome, but I do not know how to make it more streamlined.

    Code:
    If Me.tglMultiItem = True Then
    'Multi_Item Order
        Select Case Me.cbxContractCo
        Case "Other"
            For i = 0 To lstMultiItem.ListCount - 1
               Me.lstMultiItem.SetFocus
               varItem = lstMultiItem.ItemData(i) 'get next item in list data
               lstBox = varItem     'set listbox to the item
               strSKU = lstBox.Column(0)
               intQty = lstBox.Column(1)
               strProcess = lstBox.Column(2)
               Debug.Print strSKU
               Debug.Print intQty
               Debug.Print strProcess
               strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                    & varProcess & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & varSKU & "'," & varQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
               CurrentDb.Execute strRecordSQL
            Next
       Case "Contract - Larq"
                For i = 0 To lstMultiItem.ListCount - 1
                    varItem = lstMultiItem.ItemData(i) 'get next item in list data
                    lstBox = varItem     'set listbox to the item
                    varSKU = lstBox.Column(0)  'in vb, columns start with zero
                    varQty = lstBox.Column(1)
                    varProcess = lstBox.Column(2)
                If CInt(DLookup("SaleableQty", "Inventory", "Inventory.SKU = varSKU")) < CInt(varQty) Then
                    MsgBox "There is insufficient item inventory of " & varSKU & "to fulfill this order. Please notify manager.", vbOKOnly
                    Next
                Else
                End If
                   strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                        & varProcess & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & varSKU & "'," & varQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
                   CurrentDb.Execute strRecordSQL
                   strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty -" & Me.tbxSKUQty.Value & " WHERE SKU  = '" & Me.cbxSKU & "';"
                   DoCmd.RunSQL strUpdateQtySQL
                Next
                    Me.lstSerialNos.SetFocus
                    For nIndex = 0 To lstSerialNos.ListCount - 1
                    strSerialList = strSerialList & lstSerialNos.ItemData(nIndex) & ","
                    Next
                    strSerialList = Left$(strSerialList, Len(sList) - 1)
                    Debug.Print strSerialList
                    strRecordLarqSQL = "INSERT INTO LarqData (LarqOrder, LarqSerial) VALUES (Me.tbxOrderNo.Value , strSerialList)"
                    CurrentDb.Execute strRecordLarqSQL
        Case Else
            For i = 0 To lstMultiItem.ListCount - 1
                    varItem = lstMultiItem.ItemData(i) 'get next item in list data
                    lstBox = varItem     'set listbox to the item
                    varSKU = lstBox.Column(0)  'in vb, columns start with zero
                    varQty = lstBox.Column(1)
                    varProcess = lstBox.Column(2)
            If CInt(DLookup("SaleableQty", "Inventory", "Inventory.SKU = varSKU")) < CInt(varQty) Then
                        MsgBox "There is insufficient item inventory of " & varSKU & "to fulfill this order. Please notify administrator.", vbOKOnly
                        Next
            Else
                   strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                        & varProcess & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & varSKU & "'," & varQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
                   CurrentDb.Execute strRecordSQL
                   strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty -" & Me.tbxSKUQty.Value & " WHERE SKU  = '" & Me.cbxSKU & "';"
                   DoCmd.RunSQL strUpdateQtySQL
             Next
    
    Else
    'Single Item Order
        If Me.cbxContractCo = "Contract - Other" Then
            strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                & Me.cbxProcess.Column(0) & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & Me.cbxSKU.Value & "'," & Me.tbxSKUQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
            CurrentDb.Execute strRecordWOSQL
        Else
            strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                & Me.cbxProcess.Column(0) & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & Me.cbxSKU.Value & "'," & Me.tbxSKUQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
            CurrentDb.Execute strRecordWOSQL
            strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty -" & Me.tbxSKUQty.Value & " WHERE SKU  = '" & Me.cbxSKU & "';"
            DoCmd.RunSQL strUpdateQtySQL
            If Me.cbxContractCo = "Contract - Larq" Then
                strRecordLarqSQL = "INSERT INTO LarqData (LarqOrder, LarqSerial) VALUES ('" & Me.tbxOrderNo.Value & "','" & Me.tbxSerialNo.Value & "')"
                CurrentDb.Execute strRecordLarqSQL
            Else
            End If
        End If
    End If
    Any insights or advice would we appreciated.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Remove the red Next. You already have a Next lower down.

    Need to concatenate varSKU variable. Assume this is a text field so need apostrophe delimiters.
    Code:
    If CInt(DLookup("SaleableQty", "Inventory", "SKU = '" & varSKU & "'"))
    Same issues under the Case Else block as well as missing End If.

    Proper indentation would make code easier to read and see the block start and stop pairs.

    There is no need for Else if no code there.
    Code:
       Case "Contract - Larq"
            For i = 0 To lstMultiItem.ListCount - 1
                varItem = lstMultiItem.ItemData(i) 'get next item in list data
                lstBox = varItem     'set listbox to the item
                varSKU = lstBox.Column(0)  'in vb, columns start with zero
                varQty = lstBox.Column(1)
                varProcess = lstBox.Column(2)
                If CInt(DLookup("SaleableQty", "Inventory", "Inventory.SKU = '" & varSKU & "'")) < CInt(varQty) Then
                    MsgBox "There is insufficient item inventory of " & varSKU & "to fulfill this order. Please notify manager.", vbOKOnly
                End If
                strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                    & varProcess & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & varSKU & "'," & varQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
                CurrentDb.Execute strRecordSQL
                strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty -" & Me.tbxSKUQty.Value & " WHERE SKU  = '" & Me.cbxSKU & "';"
                DoCmd.RunSQL strUpdateQtySQL
            Next
            Me.lstSerialNos.SetFocus
            For nIndex = 0 To lstSerialNos.ListCount - 1
                strSerialList = strSerialList & lstSerialNos.ItemData(nIndex) & ","
            Next
            strSerialList = Left$(strSerialList, Len(sList) - 1)
            Debug.Print strSerialList
            strRecordLarqSQL = "INSERT INTO LarqData (LarqOrder, LarqSerial) VALUES (Me.tbxOrderNo.Value , strSerialList)"
            CurrentDb.Execute strRecordLarqSQL
        Case Else
            For i = 0 To lstMultiItem.ListCount - 1
                varItem = lstMultiItem.ItemData(i) 'get next item in list data
                lstBox = varItem     'set listbox to the item
                varSKU = lstBox.Column(0)  'in vb, columns start with zero
                varQty = lstBox.Column(1)
                varProcess = lstBox.Column(2)
                If CInt(DLookup("SaleableQty", "Inventory", "Inventory.SKU = '" & varSKU & "'")) < CInt(varQty) Then
                    MsgBox "There is insufficient item inventory of " & varSKU & "to fulfill this order. Please notify administrator.", vbOKOnly
                End If
                strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                    & varProcess & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & varSKU & "'," & varQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
                CurrentDb.Execute strRecordSQL
                strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty -" & Me.tbxSKUQty.Value & " WHERE SKU  = '" & Me.cbxSKU & "';"
                DoCmd.RunSQL strUpdateQtySQL
            Next

    Last edited by June7; 05-21-2020 at 08:15 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I try to simplify complex if-if-then-else-else-if-else-elseif-endif-endif-else-endif spaghetti code by using Exit Sub (or Exit Function) instead of Else. If the IF is true and the else will never happen, you can use Exit to get out of the procedure, which simplifies any following code. If you have an error handler with cleanup code in the error handler's exit tag, you can use GOTO Error_Exit instead of Exit Sub.

  8. #8
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Regarding the red Next, I originally put it in so that if there was not enough inventory, it would not run the SQL, and go to the next item. If there is sufficient inventory, it would run the SQL, then go to the next item.

    I tested with 2 items in the listbox for Larq. It only inserted the first item in the listbox, but the SKU and Process fields were blank.

    And the iteration that is creating the csv list (string) of serial numbers is remaining blank.

    Code:
    Me.lstSerialNos.SetFocus
                For nIndex = 0 To lstSerialNos.ListCount - 1
                    strSerialList = strSerialList & Me.lstSerialNos.ItemData(nIndex) & ","
                Next
                strSerialList = Left$(strSerialList, Len(strSerialList) - 1)
                MsgBox strSerialList

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Again, concatenate variables, reference to textbox is a variable. If field is text type, use apostrophe delimiters. I missed this one in my earlier edit.
    Code:
    strRecordLarqSQL = "INSERT INTO LarqData (LarqOrder, LarqSerial) VALUES ('" & Me.tbxOrderNo & "', '" & strSerialList & "')"
    I think you misunderstand how For Next work - they must be in pairs. They define a looping structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Thank you. I was misunderstanding how they must be paired. The Next cannot be part of the If, as well as part of the Else. Would there be to skip the SQL and just go to the Next when there isn't sufficient inventory?

    Apologies on missing the variables.

    Do they need to be concatenated in the code where I am trying to build the string of comma separated serial numbers?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not quite sure what you want but consider this logic:
    Code:
        If CInt(DLookup("SaleableQty", "Inventory", "Inventory.SKU = '" & varSKU & "'")) < CInt(varQty) Then
                MsgBox "There is insufficient item inventory of " & varSKU & " to fulfill this order. Please notify administrator.", vbOKOnly
        Else
                strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                    & varProcess & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & varSKU & "'," & varQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
                CurrentDb.Execute strRecordSQL
                strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty -" & Me.tbxSKUQty.Value & " WHERE SKU  = '" & Me.cbxSKU & "';"
                DoCmd.RunSQL strUpdateQtySQL
        End If
    Saving aggregate data is usually unnecessary and bad design. Inventory balance should be calculated from raw data when needed.

    The variable reference (listbox items) is already being concatentated to build CSV string.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    OK. I think that I am close. It is only inserting the last record from lstMultiItem, and not each one. It is also still not creating the string from lstSerialNos. I am sure that I am missing something.

    Code:
    Case "Contract - Larq"
                For i = 0 To lstMultiItem.ListCount - 1
                    varItem = lstMultiItem.ItemData(i) 'get next item in list data
                    lstMultiItem = varItem     'set listbox to the item
                    varSKU = lstBox.Column(0)  'in vb, columns start with zero
                    varQty = lstBox.Column(1)
                    varProcess = lstBox.Column(2)
                    If CInt(DLookup("SaleableQty", "Inventory", "Inventory.SKU = '" & varSKU & "'")) < CInt(" & varQty & ") Then
                        MsgBox "There is insufficient item inventory of " & varSKU & "to fulfill this order. Please notify manager.", vbOKOnly
                    End If
                    strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
                        & varProcess & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & varSKU & "'," & varQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
                    CurrentDb.Execute strRecordSQL
                    strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty -" & varQty & " WHERE SKU  = '" & varSKU & "';"
                    DoCmd.RunSQL strUpdateQtySQL
                Next
                Me.lstSerialNos.SetFocus
                For nIndex = 0 To Me.lstSerialNos.ListCount - 1
                    strSerialList = strSerialList & Me.lstSerialNos.ItemData(nIndex) & ","
                Next
                strSerialList = Left$(strSerialList, Len(strSerialList) - 1)
                MsgBox strSerialList
                strRecordLarqSQL = "INSERT INTO LarqData (LarqOrder, LarqSerial) VALUES (Me.tbxOrderNo , strSerialList)"
                CurrentDb.Execute strRecordLarqSQL
    Thanks again for the patience.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do you really need to loop through all items in listbox or only selected items? See example of looping selected items http://allenbrowne.com/ser-50.html

    If there is insufficient item inventory do you still want to execute SQL actions? Because that's what your code does.

    You set a variable strRecordWOSQL but you Execute strRecordSQL. Do you have Option Explicit in module header? Make sure every module has this line so spelling errors of variables will be revealed. Can set VBE to automatically add this line for new modules: Tools > Options > Editor > check Require Variable Declaration.

    If you need more help, please provide db for analysis. Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Yes, I have to iterate through all of the listbox items. I am using the listbox as a visual display for multiple items on the same order.

    No, if there is not sufficient inventory, I want am going to exit sub after the msgbox. I am adding that in now.

    I am also adding Option Explicit to every module.

    This is my most confounding problem.
    Code:
    For nIndex = 0 To Me.lstSerialNos.ListCount - 1
                    strSerialList = strSerialList & Me.lstSerialNos.ItemData(nIndex) & ","
                Next
                strSerialList = Left$(strSerialList, Len(strSerialList) - 1)
                MsgBox strSerialList
    Do the variable in the Left$() and Len() functions need to be concatenated?


    It is not creating the string. No msgox appears. I will attach the db shortly. I will have to sanitize it, as it contains client data that I cannot publish.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you consider code in post 11?

    No, concatenation is already done above that. All that line is doing is chopping off trailing comma.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problems with subreports and IIF statments
    By ByronC in forum Reports
    Replies: 4
    Last Post: 12-15-2016, 02:42 PM
  2. randomizing statments on report
    By BatmanMR287 in forum Reports
    Replies: 2
    Last Post: 07-12-2015, 01:34 PM
  3. If statments??!!
    By wmagargle in forum Programming
    Replies: 4
    Last Post: 08-30-2012, 02:45 PM
  4. How to iterate through a custom collection
    By vicrauch in forum Programming
    Replies: 6
    Last Post: 07-21-2011, 02:51 PM
  5. INSERT INTO with listbox
    By TheShabz in forum Programming
    Replies: 6
    Last Post: 11-30-2010, 12:19 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums