How to avoid the duplicate values

Question

When I add the value from database, the listbox should avoid the duplicate value.

For Example

List1 box

 Value

100
200'
300

From the table, when i add the same value 001, the listbox should avoid the value. When I try to add 400, the listbox should allow to add.

Can't you use DISTINCT keyword in you SQL statement when you retrieve data? — Andrea Colleoni, Nov 16 '11 at 14:20
If Value is already available in the listbox means, what to do...? — Gopal, Nov 16 '11 at 14:30
When you say "add the same value 001" did you mean? "add the same value 100"? If so, when you say add, do you mean that you are adding it to the database, or do you mean that you are adding it in the VB code, before you populate the listbox with the database values? — jworrin, Nov 16 '11 at 14:36

score 3 · Accepted Answer · edited May 23 '17 at 12:21

3

When you create your listbox, you should use a dictionary to store all these values.

Then, you can check if the value exists:

If Not dict.Exists(key) Then 
    dict.Add key, value
    listitem.Add key
End If

See this thread for more information: Does VBA have Dictionary Structure?

edited May 23 '17 at 12:21

Community

1
1

answered Nov 16 '11 at 15:20

JMax

26,109
12
69
88

The question is for VB6 but VBA should be simple to convert. I retagged the question. – JimmyPena Nov 16 '11 at 17:30
Thanks. Seems like it works with VB6 too: http://stackoverflow.com/questions/2789830/iterate-through-a-vb6-dictionary – JMax Nov 16 '11 at 19:02

score 1 · Answer 2 · answered Nov 16 '11 at 14:21

1

Not sure if I understand your question, but I would recommend limiting the values when they are coming back from the database. With out any idea of what your database looks like, I would recommend looking up DISTINCT and using it in your query that returns the list for your listbox

answered Nov 16 '11 at 14:21

jworrin

825
1
8
20

If Value is already available in the listbox means, what to do...? – Gopal Nov 16 '11 at 14:30

score 1 · Answer 3 · answered Nov 16 '11 at 15:20

The VB6 ListBox control does not have a method to determine if it already contains a given value. You will have to use the List() and ListCount properties of the ListBox to do a search. If the ListBox has Sorted=True, you can use a binary search pattern; otherwise it will have to be a sequential search.

I am assuming that you are populating the items via AddItem, and not data bindings (DataSource, DataField etc.). If you are using data bindings, then you will need to work on the SQL, and perhaps use DISTINCT, as jworrin has recommended.

score 0 · Answer 4 · answered Nov 16 '11 at 14:45

To avoid adding duplicates, you will need to check every entry already in the list. You can get around this in domain specific ways but it depends on what else the code is doing.

But... To reiterate what jworrin said, You should restructure your database query to only get the data you want to display.

How to avoid the duplicate values

4 Answers4