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

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

0
dsg138
Asked:
dsg138
  • 7
  • 4
1 Solution
 
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
Technology Partners: 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!

 
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now