The intended outcome of this code is:
- Loop through each selection in a list box
- Check to see if unique combination of strCOID and strBroker exists already (duplicate check)
- If combination does not exist, add combination to table, move to next selection in list box
- 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