Results 1 to 3 of 3
  1. #1
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    181

    For Each value in listbox, only one value saving from list box selections

    The intended outcome of this code is:


    1. Loop through each selection in a list box
    2. Check to see if unique combination of strCOID and strBroker exists already (duplicate check)
    3. If combination does not exist, add combination to table, move to next selection in list box
    4. Else combination exists, move to next selection in list box (do not add to table)


    This code is only adding the first selection in the list box though, and ending before moving to the next selection. I think I've missed something simple. Any help?

    Code:
    Private Sub cmdAdd_Click()
    Dim oItem As Variant
    Dim strCOID As Variant
    Dim strBroker As String
    Dim strSQL As String
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    
    strCOID = Me.OpenArgs
    
    If lstCompany.ItemsSelected.Count <> 0 Then
     For Each oItem In lstCompany.ItemsSelected
         strBroker = Me.lstCompany.Column(2)
         
         'If match between Company Counter and Broker Counter does not exist
         'Insert match
         'Else move to next value
         strSQL = "SELECT * FROM tblCompanyBroker WHERE Counter = " & strCOID & " and BrokerCounter = " & strBroker & ""
         Set db = CurrentDb()
         Set rs = db.OpenRecordset(strSQL)
             If rs.recordCount = 0 Then
             strSQL = "INSERT INTO tblCompanyBroker(Counter,BrokerCounter) Values (" & strCOID & "," & strBroker & ")"
             CurrentDb.Execute strSQL
             End If
     Next
     
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub
    End If
    
    Forms![Company Form]!lstBroker.Requery
    
    DoCmd.Close acForm, "frmCompanyFormBroker"
    End Sub

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    not tested but try changing this

    strBroker = Me.lstCompany.Column(2)

    to

    strBroker = oItem.Column(2)

    at the moment you are not 'incrementing' the assignment to strBroker

  3. #3
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    181
    You got me on the right track, thank you so much!

    Solution: strBroker = lstCompany.Column(2, oItem)

    I did not know this previously, but the Column property accepts both column and row values - Column(IntCol, IntRow)

    Code:
     For Each oItem In lstCompany.ItemsSelected
         strBroker = lstCompany.Column(2, oItem)
    
         'If match between Company Counter and Broker Counter does not exist
         'Insert match
         'Else move to next value
         strSQL = "SELECT * FROM tblCompanyBroker WHERE Counter = " & strCOID & " and BrokerCounter = " & strBroker & ""
         Set db = CurrentDb()
         Set rs = db.OpenRecordset(strSQL)
             If rs.recordCount = 0 Then
                strSQL = "INSERT INTO tblCompanyBroker(Counter,BrokerCounter) Values (" & strCOID & "," & strBroker & ")"
                CurrentDb.Execute strSQL
             End If
     Next

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 07-08-2015, 09:54 AM
  2. Listbox Selections to appear on Reports
    By Atoga in forum Reports
    Replies: 8
    Last Post: 04-30-2015, 10:55 PM
  3. Replies: 3
    Last Post: 04-29-2015, 04:02 PM
  4. Replies: 2
    Last Post: 02-01-2013, 05:23 PM
  5. projects database save listbox selections
    By taya621 in forum Access
    Replies: 33
    Last Post: 01-21-2011, 10:56 AM

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