TheSwamp

Code Red => VB(A) => Topic started by: Kheilmann on October 30, 2006, 11:47:24 PM

Title: Delete Duplicates in a Combo/List box
Post by: Kheilmann on October 30, 2006, 11:47:24 PM
I am populating combo boxes based on values in a Field in a Database.  The only problem is that there are duplicates in the database (required duplicates).

How can I list each item only once?

Thanks in Advance for your help
Title: Re: Delete Duplicates in a Combo/List box
Post by: hendie on October 31, 2006, 03:40:51 AM
can't remember where I got this one

Code: [Select]
Function RemDoublers(LB As ListBox)
' Remove duplicate items from listbox
        i = 0
    Do While i < LB.ListCount
      LB.Text = LB.List(i)
      If LB.ListIndex <> i Then
        LB.RemoveItem i
      Else
        i = i + 1
      End If
    Loop
        LB.ListIndex = -1
End Function
Title: Re: Delete Duplicates in a Combo/List box
Post by: Fatty on October 31, 2006, 04:44:37 AM
I am populating combo boxes based on values in a Field in a Database.  The only problem is that there are duplicates in the database (required duplicates).

How can I list each item only once?

Thanks in Advance for your help

This works for me too

~'J'~
Title: Re: Delete Duplicates in a Combo/List box
Post by: David Hall on October 31, 2006, 09:03:30 AM
Hendie, I added that to the VBA Function area under text.  Great tool

Look here (http://www.theswamp.org/index.php?action=tpmod;dl=item53)
Title: Re: Delete Duplicates in a Combo/List box
Post by: mmelone on October 31, 2006, 11:39:10 AM
If the values you are adding to the combobox are coming from a database, the easiest solution would be to only query the database for unique values.  You take care if that in the SQL statement you use.  You will probably use the DISTINCT keyword in the SQL statement.  For example, if you are popluating a combobox with States and the table you are pulling the States from contains addresses of all your customers, the table would contain (among other things) fields like:
[Address1], [Address2], [City], [State], [Zip]

Now, the SQL statement you would use to select unique states from that list would be

"SELECT DISTINCT City FROM YourTableName ORDER BY City"

The distinct keyword decides what is distinct based upon the fields that are in the SELECT statement.  For example, if you had a table like this

addr1    city
1          Chicago
2          Chicago

and you used this SQL statement "SELECT DISTINCT * FROM Table1" or "SELECT DISTINCT addr1, city FROM Table1" you would get a recordset back with 2 rows, one for each addr (since the combination of 1 and Chicago is different then the combination of 2 and Chicago).  If you used the statement "SELECT DISTINCT city from Table1" you would get a recordset with one row in it, since there is only one unique city in the table.

Now, since all you are pulling from the database is unique data, you do not need to write any of your own deduplication code.  This should be faster as you are not adding data to the combobox that will only be removed from it later.
Title: Re: Delete Duplicates in a Combo/List box
Post by: mmelone on October 31, 2006, 11:42:08 AM
Also, it appears as if the function listed will only work if the data in the combobox is sorted.
Title: Re: Delete Duplicates in a Combo/List box
Post by: Fatty on October 31, 2006, 11:48:53 AM
Also, it appears as if the function listed will only work if the data in the combobox is sorted.
I agree, but perhaps OP want to see all the values before he
will remove dupes from this box?

~'J'~
Title: Re: Delete Duplicates in a Combo/List box
Post by: Kheilmann on October 31, 2006, 12:32:22 PM
Perfect...
Thanks for all your help guys....
Title: Re: Delete Duplicates in a Combo/List box
Post by: Fatty on October 31, 2006, 12:40:38 PM
Perfect...
Thanks for all your help guys....


Yeah, buddy
Here is a good place to get help
I can say to you secretly ... there are too many smart programmers here
(exclude me though :( )
Happy computing :laugh:

~'J'~


Title: Re: Delete Duplicates in a Combo/List box
Post by: hendie on November 01, 2006, 03:09:11 AM
well, if you want to sort it first then...

Code: [Select]
Function LBsort(LB As ListBox)
' Sort the listbox
    Dim LBvar As Variant
    For i = 0 To LB.ListCount - 2
        If LB.List(i) > LB.List(i + 1) Then
            LBvar = LB.List(i)
            LB.List(i) = LB.List(i + 1)
            LB.List(i + 1) = LBvar
            i = -1
        End If
    Next i
End Function