TheSwamp
Code Red => VB(A) => Topic started 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
-
can't remember where I got this one
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
-
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'~
-
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)
-
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.
-
Also, it appears as if the function listed will only work if the data in the combobox is sorted.
-
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'~
-
Perfect...
Thanks for all your help guys....
-
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'~
-
well, if you want to sort it first then...
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