Remove Doubles in combo box

[chongo 2002]

I am using a listbox to display numbers and each number has other values included with it. (Example: 173 - Monday - House, 173 - Tuesday - Cabin, etc.) I load all the numbers in a combo box but it repeats them as many times as they are entered as expected. How do I remove the doubles and display all the information from the number into a listbox?

(Example: 123,173,(Selected: 200)) Listbox would display: Tuesday - House
Monday - Cabin etc

any suggestions?

'loads values in combo box
With rs2
        cboremove.Clear
        Dim a As Integer
        rs2.MoveFirst
        For a = 1 To rs2.RecordCount
        cboremove.AddItem rs2![RiderNumber]
        If a <> rs2.RecordCount Then rs2.MoveNext
        Next a
End With

[JHausmann]

What does the sql look like for rs2? You could always toss a "distinct" or Max() or min() in it...

[chongo 2002]

Here is the sql:

Set rs2 = db1.OpenRecordset("SELECT * " & "FROM [entered] ORDER BY RiderNumber", dbOpenDynaset)

[JHausmann]

Originally posted by chongo 2002
Here is the sql:

Set rs2 = db1.OpenRecordset("SELECT * " & "FROM [entered] ORDER BY RiderNumber", dbOpenDynaset)

Nope, it doesn't look like those will work for you. Problem is you have 2 pieces of information in one field. You could create arrays to save the two parts, then load the list box from the array(s).

It's not very elegant, but you could check to see if the entry is already in the box prior to adding it.

Function Text_In_List(LstBox As ListBox, Txtline As String) As Integer
'This routine will return the index of the matching entry to a ListBox.
' A -1 indicates no match

Dim varitm As Variant, result As Integer, i As Integer

i = LstBox.ListCount - 1
On Error GoTo Xit_Text_In_List
For result = i To 0 Step (-1)
If LstBox.List(result) = Txtline Then GoTo Xit_Text_In_List
Next result

Xit_Text_In_List:
Text_In_List = result
End Function



It will take a while, but it will also work.


Good Luck
DerFarm

[chongo 2002]

The code worked and the time doesn't matter since I have other slow functions as well. I am not dealing with that many records either.