Search Function in Access VBA Treeview

Experts,
I have an Access Treeview Control.  I have about 200 different records in the treeview so a search function is now becoming necessary.  My TreeView is called "xTree".  I have some code which I compiled using some other EE posts. (see below)

When I run the code below, it finds and highlights the first time it finds a record.
Clicking "search_Click" again does nothing... It stays on the first record it finds and does not move to other records.  I need the click button to work as a next button and keep cycling through to the other records it finds like the actual Built In Search. (or if anyone has any better ideas on who this should work)

Also, instead of hardcoding the search term (like I did with Medication), what would be the right syntax to take this from my txtbox?  I treid the following unsuccessfully:  
SearchNow " & [frmTree2].[SearchBox] & ", xTree.Nodes.Item(1)   ' search from beginnning

Thanks in advance,

-dsg
Private Sub search_Click()   
SearchNow "Medication", xTree.Nodes.Item(1)   ' search from beginnning
End Sub
 
 
 
Private Sub SearchNow(SearchStr As String, SelectItem As Node)
    Dim I As Integer, J As Integer
    
    On Error Resume Next
    For I = 1 To xTree.Nodes.Count
        If (xTree.Nodes.Item(I).Key = SelectItem.Key) Then Exit For
    Next
    For J = I + 1 To xTree.Nodes.Count
        If (InStr(1, xTree.Nodes.Item(J).Text, SearchStr)) Then
            xTree.Nodes.Item(J).EnsureVisible
            Set xTree.SelectedItem = xTree.Nodes.Item(J)
            Exit For
        End If
    Next
    xTree.SetFocus
    If (J >= xTree.Nodes.Count) Then
        MsgBox "Search complete. No More Found.", vbOKOnly + vbInformation, "Search"
    End If
End Sub

Open in new window

dsg138Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

danaseamanCommented:
Try this code:

Private Sub search_Click()
   Dim nod As Node
   Set nod = xTree.SelectedItem ' search from selected
   If nod Is Nothing Then
      Set nod = xTree.Nodes.Item(1) ' search from beginnning
   End If
   SearchNow "Sub", nod, xTree
End Sub
 
Private Sub SearchNow(SearchStr As String, SelectItem As Node, tv As TreeView)
    Dim I As Integer
     
    On Error Resume Next
    For I = SelectItem.Index + 1 To tv.Nodes.Count
        If (InStr(1, tv.Nodes.Item(I).Text, SearchStr)) Then
            tv.Nodes.Item(I).EnsureVisible
            Set tv.SelectedItem = tv.Nodes.Item(I)
            Exit For
        End If
    Next
    tv.SetFocus
    If (I >= tv.Nodes.Count) Then
        MsgBox "Search complete. No More Found.", vbOKOnly + vbInformation, "Search"
    End If
End Sub

Open in new window

0
danaseamanCommented:
Also change:
    If (I >= tv.Nodes.Count) Then
        MsgBox "Search complete. No More Found.", vbOKOnly + vbInformation, "Search"
    End If
To:
    If (I > tv.Nodes.Count) Then
        MsgBox "Search complete. No More Found.", vbOKOnly + vbInformation, "Search"
    End If
0
dsg138Author Commented:
Thanks for your help....
I like your idea here.
When I run the code, it stops here on this line:  SearchNow "Sub", nod, xTree
With a Run-Time error #13, Type Mismatch

When I watch it in VB, nod correctly populates with the correct string from the treeview.

Any idea about the Type Mismatch?

Thanks again.

-dsg
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

danaseamanCommented:
Runs OK here.
Using F9 Put a break point on "SearchNow "Sub", nod, xTree"
Run the program and when it stops on above line single-step with F8 to see where the mismatch is.
0
dsg138Author Commented:
When it stops on that line, 1 Single Step stops on that line.  So there's something it doesn't like about that function call.

I tried some watches also.
Nod is correctly showing the Selected Node.
xTree.Nodes.Item(1) correctly shows the first node in the tree
xTree.SelectedItem correctly shows the Selected Node.

I don't think it ever makes it down to the SearchNow function.

Not sure if this helps, but the Nodes in my tree are made up of a String of Values:
rst![Title] & " - " & rst![Code] & " - " & rst![PID] & " - " & rst![QorS]

So a Typical Node would look like this:
Known Allergies - DBTQ0058 - 710 - Q
Member's Other Concerns or Issues - DBTQ0059 - 709 - Q
Medication Side Effects - DBTQ9022 - 715 - Q

In the sample I originally posted, a search for "Medication" would select the Node that says:
Medication Side Effects - DBTQ9022 - 715 - Q
But it would stop on that line and not find any other line that has the word "Medication" in it.

I think your SearchNow function should take care of that, but the type mismatch is occuring before it gets to run through SearchNow.
0
danaseamanCommented:
It is probably the tv As Treeview since I am testing on Vb6. You can remove that parameter and replace all the lines SearchNow that have "tv." and rename to your "xTree.".

0
danaseamanCommented:
Revised code:

Private Sub cmdSearch_Click()
   Dim nod As Node
   Set nod = xTree.SelectedItem ' search from selected
   If nod Is Nothing Then
      Set nod = xTree.Nodes.Item(1) ' search from beginnning
   End If
   SearchNow "Sub", nod
End Sub
 
Private Sub SearchNow(SearchStr As String, SelectItem As Node)
    Dim i As Integer
     
    On Error Resume Next
    For i = SelectItem.Index + 1 To xTree.Nodes.Count
        If (InStr(1, xTree.Nodes.Item(i).Text, SearchStr)) Then
            xTree.Nodes.Item(i).EnsureVisible
            Set xTree.SelectedItem = xTree.Nodes.Item(i)
            Exit For
        End If
    Next
    xTree.SetFocus
    If (i > xTree.Nodes.Count) Then
        MsgBox "Search complete. No More Found.", vbOKOnly + vbInformation, "Search"
    End If
End Sub

Open in new window

0
dsg138Author Commented:
Thank you!  This worked.  It now searches through the records.
I have 2 questions.

1.  Can the SearchNow function start at the First Node no matter what node is selected?  Currently, If I have record 150 of 200 selected, this will only search in the remaining 50 records.  I would be fine if it always started each search at the first Node.

2. I modified search_Click() to take the search string from a txtbox called SearchBox instead of Hard-Coding the Search String in the Code.  This works correctly when I have a value in the textbox, but when the box is empty, I get an "Invalid Use of Null" on my function call to SearchNow.  I have no idea how it even gets to that function since my If statement should prohibited that from happening.  Do you see anything wrong in that code?  Code Attached...

Thanks for all your help...



Private Sub search_Click()
   Dim nod As Node
      
   If Me.SearchBox.Value = Null Then
        MsgBox "You must enter a value before searching."
        GoTo SearchExit
   Else
        Set nod = xTree.SelectedItem ' search from selected
        If nod Is Nothing Then
           Set nod = xTree.Nodes.Item(1) ' search from beginnning
        End If
        'SearchNow "Sub", nod
        SearchNow Me.SearchBox.Value, nod
   End If
    
SearchExit:
   Exit Sub
 
End Sub

Open in new window

0
danaseamanCommented:
Sorry for delay, Exchange was down.
Try this:

Private Sub search_Click()
   If Len(Me.SearchBox.Text) Then
      SearchNow "Sub"
   End If
End Sub
 
Private Sub SearchNow(SearchStr As String)
   Dim i                As Integer
   Static lItemIndex    As Long
   
   On Error Resume Next
   For i = lItemIndex + 1 To xTree.Nodes.Count
      If (InStr(1, xTree.Nodes.Item(i).Text, SearchStr)) Then
         xTree.Nodes.Item(i).EnsureVisible
         Set xTree.SelectedItem = xTree.Nodes.Item(i)
         lItemIndex = i 'Set to item found
         Exit For
      End If
   Next
   xTree.SetFocus
   If (i > xTree.Nodes.Count) Then
      lItemIndex = 1 'Reset to top
      MsgBox "Search complete. No More Found.", vbOKOnly + vbInformation, "Search"
   End If
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
danaseamanCommented:
OOPS:
Private Sub search_Click()
   If Len(Me.SearchBox.Text) Then
      SearchNow Me.SearchBox.Text
   End If
End Sub

0
dsg138Author Commented:
Perfect!  Thank you for all of your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.