Author Topic: Delete Duplicates in a Combo/List box  (Read 3298 times)

0 Members and 1 Guest are viewing this topic.

Kheilmann

  • Guest
Delete Duplicates in a Combo/List box
« 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

hendie

  • Guest
Re: Delete Duplicates in a Combo/List box
« Reply #1 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

Fatty

  • Guest
Re: Delete Duplicates in a Combo/List box
« Reply #2 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'~

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: Delete Duplicates in a Combo/List box
« Reply #3 on: October 31, 2006, 09:03:30 AM »
Hendie, I added that to the VBA Function area under text.  Great tool

Look here
« Last Edit: October 31, 2006, 09:23:46 AM by CmdrDuh »
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

mmelone

  • Guest
Re: Delete Duplicates in a Combo/List box
« Reply #4 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.

mmelone

  • Guest
Re: Delete Duplicates in a Combo/List box
« Reply #5 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.

Fatty

  • Guest
Re: Delete Duplicates in a Combo/List box
« Reply #6 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'~

Kheilmann

  • Guest
Re: Delete Duplicates in a Combo/List box
« Reply #7 on: October 31, 2006, 12:32:22 PM »
Perfect...
Thanks for all your help guys....

Fatty

  • Guest
Re: Delete Duplicates in a Combo/List box
« Reply #8 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'~



hendie

  • Guest
Re: Delete Duplicates in a Combo/List box
« Reply #9 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