By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,770 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

Search in Listbox via Textbox

P: 20
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW PartID, Category, Engine, Model, Brand FROM EX ORDER BY PartID; 
Code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdRun2_Click()
  2.  
  3. Dim i As Integer
  4. Dim A As Integer
  5. Dim txtSelect1 As String
  6. Dim response As Integer
  7.  
  8. For i = 0 To listPart.ListCount - 1
  9.  
  10.     'To determine Selected-Combo-Box and the Search Column of the listbox
  11.     If qSelect1 = "PartID" Then
  12.         A = 0
  13.  
  14.         ElseIf qSelect1 = "Category" Then
  15.                A = 1
  16.  
  17.                 ElseIf qSelect1 = "Engine" Then
  18.                    A = 2
  19.  
  20.                     ElseIf qSelect1 = "Model" Then
  21.                         A = 3
  22.  
  23.                         ElseIf qSelect1 = "Brand" Then
  24.                             A = 4
  25.     End If
  26.  
  27.     listPart.Selected(i) = False
  28.  
  29.     'Try to find the closest match
  30.     txtSelect1 = "" & txtSelect1 & " * "
  31.  
  32.     'After it determine which Column to search, "A" represent it
  33.     If listPart.Column(A, i) Like txtSelect1 Then
  34.  
  35.     listPart.Selected(i) = True
  36.  
  37.     listPart.SetFocus
  38.  
  39.     'After the data had been search & selected, it will auto Order By 
  40.     response = listOrderBy(qSelect1, "ASC")
  41.  
  42.     End If
  43.  
  44. Next
  45.  
  46. 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.
Jul 29 '09 #1
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 1,287
Line 30 should reference the value of the text box on the form. No guarantees about the rest of the code, though.
Jul 29 '09 #2

P: 20
txtSelect1 is the Textbox's name

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

Expand|Select|Wrap|Line Numbers
  1. listPart.Selected(I) = False
  2.  
  3.     'Able to find the closest match. I remove this line by commenting it.
  4.     'txtSelect1 = "" & txtSelect & " * "
  5.  
  6.     'After it determine which Column to search, "A" represent it
  7.     'Without wildcard search, I used equal = sign
  8.     If listPart.Column(0, I) = txtSelect Then
  9.  
  10.     listPart.Selected(I) = True
  11.  
  12.     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 *
Jul 30 '09 #3

100+
P: 675
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 30 '09 #4

Expert 100+
P: 1,287
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.
Jul 30 '09 #5

100+
P: 675
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 30 '09 #6

Expert 100+
P: 1,287
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.
Jul 30 '09 #7

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdRun2_Click()
  2.  
  3. Dim I As Integer
  4. Dim A As Integer
  5. Dim strSelect As String
  6. 'Dim response As Integer
  7.  
  8. 'To determine Selected-Combo-Box and the Search Column of the listbox
  9. If qSelect1 = "PartID" Then
  10.         A = 0
  11.  
  12.         ElseIf qSelect1 = "Category" Then
  13.         A = 1
  14.  
  15.                 ElseIf qSelect1 = "Engine" Then
  16.                 A = 2
  17.  
  18.                     ElseIf qSelect1 = "Model" Then
  19.                     A = 3
  20.  
  21.                         ElseIf qSelect1 = "Brand" Then
  22.                         A = 4
  23. End If
  24.  
  25. For I = 0 To listPart.ListCount - 1
  26.  
  27.     listPart.Selected(I) = False
  28.  
  29.     'Able to find the closest match
  30.     strSelect = txtSelect1 & "*"
  31.  
  32.     'After it determine which Column to search, "A" represent it
  33.     If listPart.Column(A, I) Like strSelect Then
  34.  
  35.     listPart.Selected(I) = True
  36.  
  37.     listPart.SetFocus
  38.  
  39.     'After the data had been search & selected, it will auto Order By
  40.     'response = listOrderBy(qSelect1, "ASC")
  41.  
  42.     End If
  43.  
  44. Next
  45.  
  46. 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?
Jul 31 '09 #8

Expert 100+
P: 1,287
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.
Aug 3 '09 #9

P: 20
Thanks a lot, ChipR. Really appreciate all your help.
Aug 5 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.