• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

search all the column in listview with wildcard

Hi guys,

 Pls provide some code to search all the column in listview with wildcard. Pls advice
0
aarontham
Asked:
aarontham
  • 12
  • 11
  • 2
1 Solution
 
danaseamanCommented:
Option Explicit

Private Sub Form_Load()
    Dim bResult As Boolean
    Dim i As Long
    Const Pattern As String = "?ab*"
   
    For i = 1 To ListView1.ListItems.Count
       bResult = ListView1.ListItems(i).Text Like Pattern
       If bResult Then
          Debug.Print "Match found at ListView1.ListItems "; i
       End If
    Next
End Sub
0
 
aaronthamAuthor Commented:
Hi danaseaman,

your code have below error.

Compile error:
Constant expression required.

in line

Const Pattern As String = "?ab*"


I need the code can highlight the search result in the same listview. Pls advice.

0
 
Mark_FreeSoftwareCommented:

dim pattern as string
Pattern = "?ab*"




**please do not give points to this answer, since danaseaman answered your question, i just corrected an error of him**
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
aaronthamAuthor Commented:
hi Mark_FreeSoftware,

after i follow your code but still don't work.Pls advice
0
 
Mark_FreeSoftwareCommented:

does it prints some text to the debug screen?
0
 
Mark_FreeSoftwareCommented:
try the following code:
(it is the modified version of the code from danaseaman )

Option Explicit

Private Sub Form_Load()
    Dim bResult As Boolean
    Dim i As Long
    Dim Pattern As String
    Pattern = "?ab*"
   
   Fillist
   
   
    For i = 1 To ListView1.ListItems.Count
       ListView1.ListItems(i).Selected = False
       bResult = ListView1.ListItems(i).Text Like Pattern
       If bResult Then
          Debug.Print "Match found at ListView1.ListItems "; i
          ListView1.ListItems(i).Selected = True
       End If
    Next
End Sub


Private Sub Fillist()
   ListView1.ListItems.Add , , "abcd"
   ListView1.ListItems.Add , , "1abcd"
   ListView1.ListItems.Add , , "2abcd"
   ListView1.ListItems.Add , , "3abcd"
   ListView1.ListItems.Add , , "4abcd123"
   ListView1.ListItems.Add , , "abcd4312"
   ListView1.ListItems.Add , , "abcd4"
   ListView1.ListItems.Add , , "abcd4134"
   ListView1.ListItems.Add , , "bd"
End Sub
0
 
aaronthamAuthor Commented:
hi Mark_FreeSoftware,

pls find the listview1 data like below

abc123
abc2345
abc256
567abc

when use key "5" in a text box the listview1 show result like below

abc2345
abc256
567abc

when use key "56" in a text box the listview1 show result like below

abc256
567abc

when use backspace once "56" in a text box and left "5" in the textbox and the listview1 show result like below

abc2345
abc256
567abc

Pls advice. thank you.

0
 
Mark_FreeSoftwareCommented:

can you try to spell that different, since i can't exactly follow what you are saying (english is not my native language)
0
 
danaseamanCommented:
Try using Instr instead of Like:
bResult =Instr( ListView1.ListItems(i).Text, Pattern)
0
 
aaronthamAuthor Commented:
Hi Guys,

in the form i have  1 listview1 and one textbox1.

i need a code in Private Sub Textbox1_Change()

I have listview1 populate data like below.

abc123
abc2345
abc256
567abc

when user key in "5" in the textbox1 then listview1 will show result like below
( the code will search 5 in the listview1 data and display result into listview1 )

abc2345
abc256
567abc

when user key in "56" in the textbox1 then listview1 show result like below
( the code will search 56 in the listview1 data and display result into listview1 )

abc256
567abc

when use backspace once in the textbox1 that have "56" and left "5" in the textbox and the listview1 show result like below
( the code will search 5 in the listview1 data and display result into listview1 )

abc2345
abc256
567abc
0
 
Mark_FreeSoftwareCommented:
then you could try this code:


Option Explicit

Private Sub Text1_Change()
    Dim bResult As Boolean
    Dim i As Long
    Dim Pattern As String
    Pattern = Text1.Text
    For i = 1 To ListView1.ListItems.Count
       ListView1.ListItems(i).Selected = False
       bResult = CBool(InStr(ListView1.ListItems(i).Text, Pattern))
       If bResult Then
          'Debug.Print "Match found at ListView1.ListItems "; i
          ListView1.ListItems(i).Selected = True
       End If
    Next
End Sub

Private Sub Form_Load()
   ListView1.ListItems.Add , , "abcd"
   ListView1.ListItems.Add , , "1abcd"
   ListView1.ListItems.Add , , "2abcd"
   ListView1.ListItems.Add , , "3abcd"
   ListView1.ListItems.Add , , "4abcd123"
   ListView1.ListItems.Add , , "abcd4312"
   ListView1.ListItems.Add , , "abcd4"
   ListView1.ListItems.Add , , "abcd4134"
   ListView1.ListItems.Add , , "bd"
   
   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!important!!!!!!!!!!!!!!!!!!!!!!!
   ListView1.HideSelection = False
   ListView1.MultiSelect = True
   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!important!!!!!!!!!!!!!!!!!!!!!!!
End Sub

0
 
aaronthamAuthor Commented:
Hi Mark_FreeSoftware,

the code work but i have few thousand data in the listview1. the code only highlight the data and the user still need to check 1 by 1.
can we remove all those unwanted data and only display result in the listview1. Pls advice.

Thank you in advance.
 
0
 
Mark_FreeSoftwareCommented:

oww, that's a different question again


so you want to remove items that do not match?

working on it
0
 
Mark_FreeSoftwareCommented:

this is the update text1_change sub, please note the looping starts at the end!


Private Sub Text1_Change()
    Dim bResult As Boolean
    Dim i As Long
    Dim Pattern As String
    Pattern = Text1.Text
    For i = ListView1.ListItems.Count To 1 Step -1
       ListView1.ListItems(i).Selected = False
       bResult = CBool(InStr(ListView1.ListItems(i).Text, Pattern))
       If Not bResult Then
          ListView1.ListItems.Remove i
       End If
    Next
End Sub
0
 
aaronthamAuthor Commented:
Hi Mark_FreeSoftware,

Sorry for so many question i really stuck here. Pls help.
in the textbox1 that have "56" and when use backspace once left "5" in the textbox and the listview1 show result like below
( the code will search 5 all over again in the listview1 data and display result into listview1 )

abc2345
abc256
567abc
0
 
Mark_FreeSoftwareCommented:

then you have to add all the items before doing a new search

because my last post only remove items, but it doesnt make things visible again.


what you could try is this:


add 2 listviews,
one visible one not

add all the items to the invisible listview (listview1)

then instead of removing them from listview1, add them to the visible listview2
0
 
aaronthamAuthor Commented:
Hi Mark_FreeSoftware,

How about the code in there should i change to use listview2. maybe you can help to post the new code there. By the way how to make the listview not visible.

Thank you.
0
 
Mark_FreeSoftwareCommented:

add a secondary listview, (listview2)

place it where you want to see the listview

then use this code:



Option Explicit

Private Sub Text1_Change()
    Dim bResult As Boolean
    Dim i As Long
    Dim Pattern As String
    Pattern = Text1.Text
    ListView2.ListItems.Clear
    For i = 1 To ListView1.ListItems.Count
       bResult = CBool(InStr(ListView1.ListItems(i).Text, Pattern))
       If bResult Then
          ListView2.ListItems.Add , , ListView1.ListItems(i).Text
       End If
    Next
End Sub

Private Sub Form_Load()
   'this is just test data, add your data here:
   ListView1.ListItems.Add , , "abcd"
   ListView1.ListItems.Add , , "1abcd"
   ListView1.ListItems.Add , , "2abcd"
   ListView1.ListItems.Add , , "3abcd"
   ListView1.ListItems.Add , , "4abcd123"
   ListView1.ListItems.Add , , "abcd4312"
   ListView1.ListItems.Add , , "abcd4"
   ListView1.ListItems.Add , , "abcd4134"
   ListView1.ListItems.Add , , "bd"
   
   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!important!!!!!!!!!!!!!!!!!!!!!!!
   ListView2.HideSelection = False
   ListView2.MultiSelect = True
   ListView1.Visible = False         'make listview1 invisible
   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!important!!!!!!!!!!!!!!!!!!!!!!!
End Sub
0
 
aaronthamAuthor Commented:
Hi Mark_FreeSoftware,

sorry to disturb you again.

in the listview1 i have column2 and column3 like below. which i use to link to other Database.

Below is the column name
Desc                BrandID            NameID
 abcd                     1                     2
 1abcd                   1                     6
 2abcd                   1                     3
 3abcd                   3                     5
 4abcd123               3                    9
 abcd4312               6                   12
 abcd4                    8                   32
 abcd4134               8                    2


I need the search result to copy the BrandID and nameID over to listview2. like below
Desc                BrandID            NameID
 abcd4                    8                   32
 abcd4134               8                    2

Thank You,


0
 
Mark_FreeSoftwareCommented:

can you show me the code how you fill the listview?
0
 
aaronthamAuthor Commented:
Dear mark,

Pls find below fill listview code.


Public Sub LoadBrand()
Dim strQuery1 As String
Dim itX As ListItem
Call initDB

Set rs1 = New ADODB.Recordset
On Error GoTo errorTrap
'Load Brand Into Listview6
strQuery1 = "Select Brand,BrandID From brandt"

ListView6.ListItems.Clear

rs1.Open strQuery1, cn, adOpenDynamic, adLockOptimistic
If Not rs1.EOF Then
    rs1.MoveFirst
 
    Do Until rs1.EOF
       
    If Not IsNull(rs1.Fields("Brand")) Then
    Set itX = ListView6.ListItems.Add(, , Trim(rs1.Fields("Brand")))
    Else
    Set itX = ListView6.ListItems.Add(, , (""))

    End If
    If Not IsNull(rs1.Fields("BrandID")) Then
    itX.SubItems(1) = Trim(rs1.Fields("BrandID"))
    Else
    itX.SubItems(1) = ("")
    End If
 
        rs1.MoveNext
    Loop

End If

rs1.Close
Set rs1 = Nothing
cn.Close
Set cn = Nothing

Exit Sub
errorTrap:
MsgBox Err.Description & vbCrLf & Err.HelpContext & vbCrLf & Err.HelpFile & vbCrLf & Err.LastDllError & vbCrLf & Err.Number & vbCrLf & Err.Source

End Sub
0
 
aaronthamAuthor Commented:

Hi Mark,

Any clue yet. Pls help

thank You,
0
 
Mark_FreeSoftwareCommented:
Option Explicit

Private Sub Text1_Change()
Dim n As Long
    Dim bResult As Boolean
    Dim i As Long
    Dim Pattern As String
    Pattern = Text1.Text
    ListView2.ListItems.Clear
    For i = 1 To ListView1.ListItems.Count
       bResult = CBool(InStr(ListView1.ListItems(i).Text, Pattern))
       If bResult Then
         ListView2.ListItems.Add , , ListView1.ListItems(i).Text
         For n = 1 To 2
            ListView2.ListItems(i).SubItems(n) = ListView1.ListItems(i).SubItems(n)
         Next
       End If
    Next
End Sub

Private Sub Form_Load()
Dim itX As ListItem, a As Long
   
   For a = 1 To 3
      ListView1.ColumnHeaders.Add
      ListView2.ColumnHeaders.Add
    Next
   
   
   Set itX = ListView1.ListItems.Add(, , "abcd")
   ListView1.ListItems(1).SubItems(1) = "aa"
   ListView1.ListItems(1).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "1abcd")
   ListView1.ListItems(2).SubItems(1) = "aa"
   ListView1.ListItems(2).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "2abcd")
   ListView1.ListItems(3).SubItems(1) = "aa"
   ListView1.ListItems(3).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "3abcd")
   ListView1.ListItems(4).SubItems(1) = "aa"
   ListView1.ListItems(4).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "4abcd123")
   ListView1.ListItems(5).SubItems(1) = "aa"
   ListView1.ListItems(5).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "abcd4312")
   ListView1.ListItems(6).SubItems(1) = "aa"
   ListView1.ListItems(6).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "abcd4")
   ListView1.ListItems(7).SubItems(1) = "aa"
   ListView1.ListItems(7).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "abcd4134")
   ListView1.ListItems(8).SubItems(1) = "aa"
   ListView1.ListItems(8).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "bd")
   ListView1.ListItems(9).SubItems(1) = "aa"
   ListView1.ListItems(9).SubItems(2) = "bb"
   
   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!important!!!!!!!!!!!!!!!!!!!!!!!
   ListView2.HideSelection = False
   ListView2.MultiSelect = True
   ListView1.Visible = False
   ListView2.View = lvwReport
   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!important!!!!!!!!!!!!!!!!!!!!!!!
End Sub

0
 
aaronthamAuthor Commented:
Dear Mark,

When there is 1 result let it have below error.


run-time error '35600'

Index out of bounds

Error in below line code.

           ListView2.ListItems(i).SubItems(n) = ListView1.ListItems(i).SubItems(n)

Pls advice.
0
 
Mark_FreeSoftwareCommented:

add some error trapping in text1_change

like:
on error resume next


or:
private su.....
on error goto lblError1

'the code

exit sub
lblError1:
if err.number = 35600 then resume next
end sub
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 11
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now