Link to home
Start Free TrialLog in
Avatar of dsg138
dsg138

asked on

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

Avatar of Dana Seaman
Dana Seaman
Flag of Brazil image

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

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
Avatar of dsg138
dsg138

ASKER

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
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.
Avatar of dsg138

ASKER

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

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

Avatar of dsg138

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Dana Seaman
Dana Seaman
Flag of Brazil image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OOPS:
Private Sub search_Click()
   If Len(Me.SearchBox.Text) Then
      SearchNow Me.SearchBox.Text
   End If
End Sub

Avatar of dsg138

ASKER

Perfect!  Thank you for all of your help!