I am using the following code to populate a combo box placed on Sheet1 of an excel file to add items from Access database table named Company.
Code:
Private Sub cboCompany_DropButtonClick()
    Dim con As Object
    Dim rs As New ADODB.Recordset
    Set con = CreateObject("ADODB.Connection")
    con.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ActiveWorkbook.Path & "\ServiceTaxData.mdb"
    With rs
            .ActiveConnection = con
            .CursorLocation = adUseClient
            .CursorType = adOpenDynamic
            .LockType = adLockOptimistic
            .Source = "select * from company"
            .Open
    End With
    cboCompany.Clear
    If rs.RecordCount < 0 Then
    MsgBox "No company name found"
    Else
    For i = 1 To rs.RecordCount
    cboCompany.AddItem rs.Fields(1)
    rs.MoveNext
    Next i
    End If
End Sub
The problem is that it adds the items in the combo box every time the arrow button is clicked ie if there are forur items in the table, on first click it adds 4 items, on 2nd click it again adds four items results in 8 items.
So I have added the line cbocompany.clear
Now the problem is that it clears the combo box items as soon as an item is selected from the list.
With debugging break mode, what I have seen that after adding the last item ie after Next I for last item it again moves to cbocompany.clear line.
How can I fix this?