Solved

How do I remove duplicate items from a VB6 listbox based on the last 2 or last 3 characters of the item?

Posted on 2010-08-27
12
817 Views
Last Modified: 2012-05-10
Using VB6 I want to pop up a message box that warns of duplicate data in a listbox.

For example, I have a listbox with the following items in the list:

P Jones_RD
T Thomas_HH
J Holcomb_Z
G Ricks_YT
H Martin_RD
N Smith_KB
H Berger_X
B Brett_VR
J Morton_CR
L Johnson_X

I need to be able to detect any duplicate extensions including the underscore; the extensions are either an underscore and one letter, or an underscore and two letters. I need to detect the duplicate occurrence of the _RD and the _X in this list and popup a MsgBox that says "Duplicate data extensions detected". Thanks for any help.
0
Comment
Question by:jazjef
12 Comments
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33548469
one option is to loop throw all items (n*n loop) and using Right/Mid function of vb, you can do it.

or before binding with listbox, you can manipulate it in your datasource.
0
 
LVL 1

Expert Comment

by:kollurirk
ID: 33548488
you can run a loop for list box in a reverse order.
check for the letter '_' using  'mid' function and remove the list content.  
0
 

Expert Comment

by:pbskat
ID: 33548682
In VB6 you have to do it manually, i.e. with a nested loop:

for i = 1 to <listbox-items.count>
  for j = i + 1 to <listbox-items.count>
    if (mid(<listboxitem(i)>, instr(<listboxitem(i)>, "_")) = _
        mid(<listboxitem(j)>, instr(<listboxitem(j)>, "_")) _
    then
        call msgbox("Duplicate item found")
        exit for
    end if
  next j
next i
0
 
LVL 4

Author Comment

by:jazjef
ID: 33549544
pbskat:

OK... using your code suggestion I have arrived at a solution that does the reverse of what I want.... it pops a message when everything to the LEFT of the "_" is duplicated in the list; I need help to reverse this code so that it does the opposite (identifies duplicates to the RIGHT of the "_")

Dim i As Integer
Dim j As Integer

For i = 1 To List1.ListCount - 1
  For j = i + 1 To List1.ListCount - 1
    If Mid(List1.List(i), 1, Len(List1.List(i)) - 3) = Mid(List1.List(j), 1, Len(List1.List(j)) - 3) Then
        MsgBox ("Duplicate item found")
    Exit For
    End If
  Next j
Next i





0
 
LVL 4

Author Comment

by:jazjef
ID: 33549553
PS:  The piece of code I just posted only references the extensions that have 2 characters after the "_".... I need it to reference both one and two characters after the "_" (However, I could just put a second loop in and use a -2 in the Len portion to catch those)
0
 
LVL 85

Accepted Solution

by:
Mike Tomlinson earned 500 total points
ID: 33549604
Try something like this:
Private Sub Command1_Click()

    On Error GoTo KeyAlreadyExists

    

    Dim i As Integer

    Dim col As New Collection

    Dim values As Variant

    Dim extension As String



    For i =  0 To List1.ListCount - 1 

        values = Split(List1.List(i), "_")

        If UBound(values) >= 1 Then

            extension = values(UBound(values))

            col.Add extension, extension

        End If

    Next i

    Exit Sub

    

KeyAlreadyExists:

    MsgBox "Duplicate data extension detected." & vbCrLf & vbCrLf & "Extension: " & extension

End Sub

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 4

Expert Comment

by:coolcurrent4u
ID: 33551106
as a general advice, the items in the listbox must be sorted before doing any looping to check for suplicates
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 33551144
...no sorting or inner looping required using the Collection technique in my post.  =)
0
 
LVL 4

Author Comment

by:jazjef
ID: 33551160
Thanks Idle_Mind, this works great! But for some reason I can't integrate it in my program. I want to call this routine whenever an item is dragged/dropped into a listbox....but even when an item that does not exist is dropped into the list, the msgbox fires.

If you don't want to continue on this question that's understandable----you've solved the problem exactly as stated in my question. I can open a new question for the drag-drop problem.
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 33551162
We'd need to see your drag/drop code and your implementation of my code to hopefully spot the problem and solve it...  =)
0
 
LVL 4

Author Comment

by:jazjef
ID: 33551250
thanks Idle_Mind.... your solution works for this problem. My actual problem is that I have 2 problems that need solving.... so, there will be another question coming down the pike....
0
 
LVL 4

Author Closing Comment

by:jazjef
ID: 33551252
Excellent.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now