Solved

search all the column in listview with wildcard

Posted on 2006-06-23
25
289 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now