Search in Listbox via Textbox

[weirdguy]

Hello,

Just for anyone information, there is a similar title "Search in Listbox" but it is via Combo Box. In case, anyone need it, I put a link to here.

Please let me know if I break any rules by posting a link so then, I will remove it.

Okay, here is my question. If you ever refer to the link, I am almost doing the same thing. Just that, I am doing a textbox and a combo box to search the listed data in the listbox.

The listbox consist of multiple column (PartID, Category, Engine, Model, Brand [Source from a Query - EX]). When user like to search, he/she can choose the combo-box (for which column to search) and type in the textbox, and finally press the Search button. Then the listbox will locate the closest/exact match and select/highlight it. A note, not filter but the listbox will Go To The Specific Data and Select/Highlight It.

My Listbox RowSource:

SELECT DISTINCTROW PartID, Category, Engine, Model, Brand FROM EX ORDER BY PartID; 

Code:

Private Sub cmdRun2_Click()
 
Dim i As Integer
Dim A As Integer
Dim txtSelect1 As String
Dim response As Integer
 
For i = 0 To listPart.ListCount - 1
 
    'To determine Selected-Combo-Box and the Search Column of the listbox
    If qSelect1 = "PartID" Then
        A = 0
 
        ElseIf qSelect1 = "Category" Then
               A = 1
 
                ElseIf qSelect1 = "Engine" Then
                   A = 2
 
                    ElseIf qSelect1 = "Model" Then
                        A = 3
 
                        ElseIf qSelect1 = "Brand" Then
                            A = 4
    End If
 
    listPart.Selected(i) = False
 
    'Try to find the closest match
    txtSelect1 = "" & txtSelect1 & " * "
 
    'After it determine which Column to search, "A" represent it
    If listPart.Column(A, i) Like txtSelect1 Then
 
    listPart.Selected(i) = True
 
    listPart.SetFocus
 
    'After the data had been search & selected, it will auto Order By 
    response = listOrderBy(qSelect1, "ASC")
 
    End If
 
Next
 
End Sub

The problem is I'm not really sure what happened. No highlighted search result after press "Search". No Error.

If you refer to the Top-Link (Search in Listbox via Combo), I try to replicate the way it code. Still, not working.

Hope someone can help. I don't mind a new way to code this, just let me know. Thanks in advance.

[ChipR]

Line 30 should reference the value of the text box on the form. No guarantees about the rest of the code, though.

[weirdguy]

txtSelect1 is the Textbox's name

Without success, I try the conventional way by removing all the wildcard search. Standard/Exact search only.

listPart.Selected(I) = False
 
    'Able to find the closest match. I remove this line by commenting it.
    'txtSelect1 = "" & txtSelect & " * "
 
    'After it determine which Column to search, "A" represent it
    'Without wildcard search, I used equal = sign
    If listPart.Column(0, I) = txtSelect Then
 
    listPart.Selected(I) = True
 
    listPart.SetFocus

It works perfectly fine, if user type the exact search-text. Is there any other way, I can add in a wildcard search. Eg. 1 * or Ha *

[OldBirdman]

I think you want to search for 'Ha*' or '1*', and not 'Ha * ' or '1 * '. I would not think you want entries beginning with 'Ha ' then some characters, then a trailing blank. Try line 30

txtSelect1 = txtSelect1 & "*" 

This will give all entries (in the appropriate column) beginning with 'Ha'. '*Ha*" will give all entries containing the string 'Ha' and so would find 'HeeHaw' as well as 'Hawfinch'.

Lines 11-25 should be moved outside the loop. The value of A will not change during the loop.

[ChipR]

Note that
txtSelect1 = txtSelect1 & "*"
is still wrong, and probably due to an error in copying the original code. You wouldn't set an empty string variable equal to an empty string variable. I would rename txtSelect1 to strSelect to avoid those needless errors.

[OldBirdman]

txtSelect1 = txtSelect1 & "*"
does not set an empty string to an empty string. If txtSelect is empty, this would highlight all entries, which is probably not what the user wants. Either this routine can do as the user enters (right or wrong), or it can do some logical checking for blank/empty textbox, trailing blanks, or whatever else might be deemed incorrect. That may be in the actual code, simplified here for posting.

I would add a string variable strSelect to this subroutine, and then

strSelect1 = txtSelect1 & "*"

replacing txtSelect1 with strSelect1 as appropriate in the code. But I framed my answer in the framework of the original code, not the code I would have written.

I would think that getting no results with wildcards, but getting results with an exact match would be because of the blanks surrounding the '*' in the compare. That is the question I was answering.

[ChipR]

I think you missed my point. txtSelect1 is a string variable declared at the beginning of the sub. txtSelect is the control on the form, judging by line 4 of post 3. If you renamed txtSelect1 to strSelect1, then that line of code would tell you that txtSelect1 was undeclared.

[weirdguy]

Thanks a lot, people. Again, thanks for your help. It works now but ...

Private Sub cmdRun2_Click()
 
Dim I As Integer
Dim A As Integer
Dim strSelect As String
'Dim response As Integer
 
'To determine Selected-Combo-Box and the Search Column of the listbox
If qSelect1 = "PartID" Then
        A = 0
 
        ElseIf qSelect1 = "Category" Then
        A = 1
 
                ElseIf qSelect1 = "Engine" Then
                A = 2
 
                    ElseIf qSelect1 = "Model" Then
                    A = 3
 
                        ElseIf qSelect1 = "Brand" Then
                        A = 4
End If
 
For I = 0 To listPart.ListCount - 1
 
    listPart.Selected(I) = False
 
    'Able to find the closest match
    strSelect = txtSelect1 & "*"
 
    'After it determine which Column to search, "A" represent it
    If listPart.Column(A, I) Like strSelect Then
 
    listPart.Selected(I) = True
 
    listPart.SetFocus
 
    'After the data had been search & selected, it will auto Order By
    'response = listOrderBy(qSelect1, "ASC")
 
    End If
 
Next
 
End Sub

but ... the search result highlighted the last record of search-text.

For an example, I want to search for anything in Column A that has B1 in front.

Search result: B12, B13, B133, B150, B190

It will highlight B190 rather than the first closest-match, B12

Anyone encounter this issue before?

[ChipR]

That is the result of your logic. In the FOR loop, you step through each item. In your example, B12 would be selected, then B13, then B133, etc., and finally B190. You can solve this by inserting "exit sub" at line 38 to stop when the first match is found.

[weirdguy]

Thanks a lot, ChipR. Really appreciate all your help.