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
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
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
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
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
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.
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.
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.
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
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...
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OOPS:
Private Sub search_Click()
If Len(Me.SearchBox.Text) Then
SearchNow Me.SearchBox.Text
End If
End Sub
Private Sub search_Click()
If Len(Me.SearchBox.Text) Then
SearchNow Me.SearchBox.Text
End If
End Sub
ASKER
Perfect! Thank you for all of your help!
Open in new window