ComboBox/Database... Removing duplicates

Hey all :-)

Looking for some help how to remove duplicate fields in a ComboBox. Im currently using a Loop function(see code below) to load the combos list. So i thought there must be something i could put in the loop that checks for already existing sections?!

Thanx in advance :-D

-Loxir


[code]
Private Sub LoadComboKlubb()

On Error GoTo Fet_Error

Do While Not rs.EOF
vntTemp = rs!Klubb
If IsNull(vntTemp) Then vntTemp = ""
ComboKlubb.AddItem CStr(vntTemp)
rs.MoveNext
Loop


ComboKlubb.DataField = "Klubb"
SQLString = "SELECT * FROM AllaAkare WHERE namn = '" & FrmAllakare.lstNamn.Text & "'"
Set rs = db.OpenRecordset(SQLString)

finish:
Exit Sub

Fet_Error:

MsgBox "Unexpected Error: " & Err.Description
End

Resume finish

End Sub
[/code]

Comments

  • : Hey all :-)
    :
    : Looking for some help how to remove duplicate fields in a ComboBox. Im currently using a Loop function(see code below) to load the combos list. So i thought there must be something i could put in the loop that checks for already existing sections?!
    :
    : Thanx in advance :-D
    :
    : -Loxir
    :
    :
    : [code]
    : Private Sub LoadComboKlubb()
    :
    : On Error GoTo Fet_Error
    :
    : Do While Not rs.EOF
    : vntTemp = rs!Klubb
    : If IsNull(vntTemp) Then vntTemp = ""
    : ComboKlubb.AddItem CStr(vntTemp)
    : rs.MoveNext
    : Loop
    :
    :
    : ComboKlubb.DataField = "Klubb"
    : SQLString = "SELECT * FROM AllaAkare WHERE namn = '" & FrmAllakare.lstNamn.Text & "'"
    : Set rs = db.OpenRecordset(SQLString)
    :
    : finish:
    : Exit Sub
    :
    : Fet_Error:
    :
    : MsgBox "Unexpected Error: " & Err.Description
    : End
    :
    : Resume finish
    :
    : End Sub
    : [/code]
    :

    You have two options.

    Load all the items in and then use the following code to get rid of duplicate entries:

    [b]If (Combo1.ListCount > 1) Then
    Dim a As Long, b As Long
    For a = Combo1.ListCount - 1 To 1 Step -1
    For b = 0 To a - 1
    If (Combo1.List(a) = Combo1.List(b)) Then
    Combo1.RemoveItem a
    Exit For
    End If
    Next b
    End If
    End If[/b]

    Or use this function to check if an item exists before adding the item:

    [b]Public Function ItemExists(ByVal pstrValue As STring) As Boolean
    ITemExists = False
    Dim a As Long
    If (Combo1.ListCount > 0) Then
    For a = 0 To Combo1.Listcount - 1
    If (Combo1.List(a) = pstrValue) Then
    ItemExists = True
    Exit Function
    End If
    Next a
    End If
    End Function[/b]

    Then when AddIng an Item:

    [b]If (ItemExists("Your Item") = False) Then
    Combo1.AddItem "Your Item"
    End If[/b]

    All the code has been written on the spot, so it may contain some bugs. If you have problems let me know.




  • Thanx alot :-)

    This is just what ive been looking for. Btw, you know which one of these codes would be preferable? e.g is one of them faster then the other? The ComboBox will contain about 200 items(at the most)

    Thanx again

    - Loxir :-)

  • : Thanx alot :-)
    :
    : This is just what ive been looking for. Btw, you know which one of these codes would be preferable? e.g is one of them faster then the other? The ComboBox will contain about 200 items(at the most)
    :
    : Thanx again
    :
    : - Loxir :-)
    :

    I'm not sure from the top of my head. Probably not going to make allot of difference. Although I would try them both to find out.

    If I was going to hazard a guess I would say it would be the one that checked to see if the item existed before adding the item to the list. And some quick mathematics which I just did seems to support that.



Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion