Solved

search all the column in listview with wildcard

Posted on 2006-06-23
25
301 Views
Last Modified: 2013-12-25
Hi guys,

 Pls provide some code to search all the column in listview with wildcard. Pls advice
0
Comment
Question by:aarontham
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 11
  • 2
25 Comments
 
LVL 22

Expert Comment

by:danaseaman
ID: 16977382
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
 

Author Comment

by:aarontham
ID: 16978174
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
 
LVL 13

Expert Comment

by:Mark_FreeSoftware
ID: 16978535

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:aarontham
ID: 16978957
hi Mark_FreeSoftware,

after i follow your code but still don't work.Pls advice
0
 
LVL 13

Expert Comment

by:Mark_FreeSoftware
ID: 16978996

does it prints some text to the debug screen?
0
 
LVL 13

Expert Comment

by:Mark_FreeSoftware
ID: 16979013
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
 

Author Comment

by:aarontham
ID: 16980936
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
 
LVL 13

Expert Comment

by:Mark_FreeSoftware
ID: 16980958

can you try to spell that different, since i can't exactly follow what you are saying (english is not my native language)
0
 
LVL 22

Expert Comment

by:danaseaman
ID: 16981209
Try using Instr instead of Like:
bResult =Instr( ListView1.ListItems(i).Text, Pattern)
0
 

Author Comment

by:aarontham
ID: 16984683
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
 
LVL 13

Expert Comment

by:Mark_FreeSoftware
ID: 16984753
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
 

Author Comment

by:aarontham
ID: 16985959
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
 
LVL 13

Expert Comment

by:Mark_FreeSoftware
ID: 16986017

oww, that's a different question again


so you want to remove items that do not match?

working on it
0
 
LVL 13

Expert Comment

by:Mark_FreeSoftware
ID: 16986097

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
 

Author Comment

by:aarontham
ID: 16989241
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
 
LVL 13

Expert Comment

by:Mark_FreeSoftware
ID: 16990794

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
 

Author Comment

by:aarontham
ID: 16994133
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
 
LVL 13

Expert Comment

by:Mark_FreeSoftware
ID: 16994518

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
 

Author Comment

by:aarontham
ID: 17006550
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
 
LVL 13

Expert Comment

by:Mark_FreeSoftware
ID: 17008776

can you show me the code how you fill the listview?
0
 

Author Comment

by:aarontham
ID: 17009297
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
 

Author Comment

by:aarontham
ID: 17028903

Hi Mark,

Any clue yet. Pls help

thank You,
0
 
LVL 13

Expert Comment

by:Mark_FreeSoftware
ID: 17029699
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
 

Author Comment

by:aarontham
ID: 17032073
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
 
LVL 13

Accepted Solution

by:
Mark_FreeSoftware earned 300 total points
ID: 17032460

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

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question