We help IT Professionals succeed at work.
Troubleshooting Question

search for exact string in a listbox

al4629740
al4629740 asked
on
100 Views
Last Modified: 2020-08-07
In the following Loop I am highlighting/selecting all the items in the listbox that match the ID records from the database.  This is in VB6.

               Do Until rec.EOF
                

                    For lngEntry = 0 To List(0).ListCount - 1
                        If InStr(1, List(0).List(lngEntry), rec!RegID) > 0 Then
                            List(0).Selected(lngEntry) = True
                            Exit For
                        End If
                    Next
                                 
                 rec.MoveNext
                 
                Loop
A typical listbox looks like this:
Doe, John - 3
Hamilton, Mark - 12
Stan, Manny - 15
Hyde, Park - 2


In my code rec!RegID contains the ID of the individual in the database.  I'm not positive but I believe that the function InStr searches a string within a string which is not what I exactly want because in the current code I have, Hyde, Park - 2 and Hamilton, Mark - 12 might cause an issue since they both have the number 2.

What I want, is to search for the unique ID string to the right of the hyphen.  How can I adjust my code to do exactly that?  Maybe my code is currently doing that and I'm mistaken, but I could use a little guidance.
Comment
Watch Question

Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Well,
there is a conception issue to begin with, because you are mixing data together (firstname, name, identifier), and you can no longer guarantee that you'll retrieve the identifier.

A Possible solution is to configure your listbox control with an hidden column (in fact, a column's width set to 0), with the identifier stored in the hidden column and everything else stored in the displayed column, this way you'll be able to perform your search with an exact match.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
@aikimark:

Law of maximum pain:
What if a data is: "McBride, Blue-Mary - 6" ?
What if an identifier is negative ?
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
1. that is why there is a space on either side of my split delimiter parameter
2. If multiple " - " strings might be found, then do the split into a variable and use the UBound() function to locate the last split result.  This is supposed to be the ID
3. If you have negative ID values, your database is in serious need of restructuring.
4. As long as there is a " - " before a negative ID value, it should still parse and compare properly.

Author

Commented:
aikimark,

I executed the following code but this time nothing was selected.  Any ideas?

Do Until rec.EOF
                

                    For lngEntry = 0 To AddCYEPHours.List(0).ListCount - 1
                        If Split(AddCYEPHours.List(0).List(lngEntry), " - ")(1) = rec!RegID Then
                            AddCYEPHours.List(0).Selected(lngEntry) = True
                            Exit For
                        End If
                    Next
                                 
                 rec.MoveNext
                 
                Loop
Social distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
AddCYEPHours is the form and this is code executing from a login screen.

When I make the change you mentioned, it returns this error:




aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
If you don't remove the .List(0) what happens?

Author

Commented:
I am unsure what you mean.  Could you clarify?  .List(0) is the name of the control.  I need it.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
Key words "don't remove" in prior comment.  Obviously, you need it to reference the name of the control.