List box like search

Hi EE

I have the following code that will do a search in a list box

intnum = Me!txtDescrip
Me!lstPart.SetFocus
With Me!lstPart
    For i = 0 To .ListCount - 1
        If .Column(2, i) Like intnum Then
            .Selected(i) = True
            Flag = True
            Exit For
        End If
    Next i
End With

although it has a like statement it will only pick up a name for example if the name search is in full ie Smith. I would like to modify or another methode to do a search by one or two characters ie search by S or Sm and it will pick up all names beginning with Sm. I know we can do this with combo box but in this instance I would like a list box. Any help appreciated

chestera
chesteraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
I'm assuming intnum is a String variable that holds the text you want to search for.  I'm not sure why you called it intnum as it suggests an number.  Anyways, to find all list items that begin with your search string, you need to use a wildcard like this:
intnum = Me!txtDescrip & "*"
To find list items that include the search string in any position (e.g. dismount), you would use this:
intnum = "*" & Me!txtDescrip & "*"
Your code will work without having to set the focus to the listbox so you could dispense with Me!lstPart.SetFocus

You should also set the value to of the listbox to the found result (using Selected(i) only highlights the item, it doesn't set the value of the listbox).

Here is your revised code:
intnum = Me!txtDescrip & "*"
With Me!lstPart
    For i = 0 To .ListCount - 1
        If .Column(2, i) Like intnum Then
            .Selected(i) = True
            .Value = .Column(2, i)
            Flag = True
            Exit For
        End If
    Next i
End With

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
you can use the change event of the textbox

private sub txtSearch_change()

dim str as string

str=str & me.txtsearch.text

me.listbox.rowsource="select id, fullname from tablex where fullname like '" & str & "*'"

end sub
0
chesteraAuthor Commented:
IrogSinta

That was a bad example I used that particular code for numerical searches. Thank you for that information

Alan
0
chesteraAuthor Commented:
capricorn1

Many thanks

Alan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.