Treeview node approprate event to find record on form

[amrut]

I have three related tables -
tblGroups- GroupID(PK), GroupName
tblSubGroups -SubGroupID(PK), SubGroupName,GroupIDFK(FK)
tblItems - ItemID(PK), ItemName,SubGroupIDFK(FK)
I have set the form-subform1-subform2 and they are alright.
I am using a treeview to load all the records in above tables and they are displayed correctly. The treeview node having ItemName as text has its tag set to the ItemID (PK) while other nodes do not have a tag.

I am trying to find a event when clicking on node will search for that record on the subform2. I tried with the nodeclick event. The event returns
the ItemID (Node.Tag) correctly but it required 2 or 3 clicks to navigate to that particular record.

To check if there is any error in my code.
I used the same code in AfterUpdate event of a Combo Box whose row source is the tblItems and it works perfectly (finds and navigates to correct record).

Private Sub cmbItems_AfterUpdate()
ShowRecord (Me.cmbItems)
End Sub

Private Sub TreeView0_NodeClick(ByVal Node As Object)
If Len(Node.Tag) <> 0 Then
Me.cmbItems = Node.Tag
ShowRecord (Node.Tag)
End If
End Sub
Sub ShowRecord(ItemID As Long)

    Dim S As String, grID As Long, subGrID As Long
        subGrID = Me.cmbItems.Column(1)   ' earlier tried DLookup("SubGroupFK", "tblItems", "[ItemID]=" & ItemID)
        grID = Me.cmbItems.Column(2)  ' earlier tried DLookup("GroupIDFK", "tblSubGroups", "[SubGroupID]=" & subGrID)

        S = "Group " & grID & vbCrLf
        S = S & "Sub Group " & subGrID & vbCrLf
        S = S & "Item " & ItemID
     Msgbox S                       ' this returns correct PK of all the tables
        'find group
        Me.RecordsetClone.FindFirst "GroupID=" & Me.cmbItems.Column(2)
        If Not Me.RecordsetClone.NoMatch Then
            Me.Bookmark = Me.RecordsetClone.Bookmark
        End If
        'find SUB group
        Me.SubForm1.SetFocus
             Me.SubForm1.Form.RecordsetClone.FindFirst "SubGroupID=" & Me.cmbItems.Column(1)
        If Not Me.SubForm1.Form.RecordsetClone.NoMatch Then
            Me.SubForm1.Form.Bookmark = Me.SubForm1.Form.RecordsetClone.Bookmark
        End If
     
        'find itemcode
        Me.SubForm1.Form!SubForm2.SetFocus
         Me.SubForm1.Form.SubForm2.Form.RecordsetClone.FindFirst "ItemID=" & ItemID
        If Not Me.SubForm1.Form.SubForm2.Form.RecordsetClone.NoMatch Then
            Me.SubForm1.Form.SubForm2.Form.Bookmark = Me.SubForm1.Form.SubForm2.Form.RecordsetClone.Bookmark
        End If

End Sub

What event or design changes in case of Treeview will achieve the same ? Your suggestions are welcome.

[orange]

Don't know the answer, but best info on treeview is by SmileyCoder on Youtube. He has 6 videos with various topics.

Good luck. Let us know if you find something helpful with treeview and search.

[TheSmileyCoder]

Hi Amrut

You are on the right track with your code to navigate the subforms. Basically its a matter of getting the additional (Item parent Key and grandparent Key) by either using dlookup, or simply navigating the treeview (Since its already there in memory)

I've tried to modify your code, see if it works now.

Sub ShowRecord(ItemID As Long)

    Dim S As String, grID As Long, subGrID As Long
        subGrID = DLookup("SubGroupFK", "tblItems", "[ItemID]=" & ItemID)
        grID =DLookup("GroupIDFK", "tblSubGroups", "[SubGroupID]=" & subGrID)

        S = "Group " & grID & vbCrLf
        S = S & "Sub Group " & subGrID & vbCrLf
        S = S & "Item " & ItemID
     Msgbox S                       ' this returns correct PK of all the tables
        'find group
        Me.RecordsetClone.FindFirst "GroupID=" & grID 
        If Not Me.RecordsetClone.NoMatch Then
            Me.Bookmark = Me.RecordsetClone.Bookmark
        End If
        'find SUB group
        Me.SubForm1.SetFocus
             Me.SubForm1.Form.RecordsetClone.FindFirst "SubGroupID=" & subGrID 
        If Not Me.SubForm1.Form.RecordsetClone.NoMatch Then
            Me.SubForm1.Form.Bookmark = Me.SubForm1.Form.RecordsetClone.Bookmark
        End If
     
        'find itemcode
        Me.SubForm1.Form!SubForm2.SetFocus
         Me.SubForm1.Form.SubForm2.Form.RecordsetClone.FindFirst "ItemID=" & ItemID
        If Not Me.SubForm1.Form.SubForm2.Form.RecordsetClone.NoMatch Then
            Me.SubForm1.Form.SubForm2.Form.Bookmark = Me.SubForm1.Form.SubForm2.Form.RecordsetClone.Bookmark
        End If

End Sub

[amrut]

Hi SmileyCoder,
Thanks for your reply. I had tried the above and tried again . I get an error 2455 ( Run-time error '2455': You entered an expression that has an invalid reference to the property Form/Report) at this line

Me.SubForm1.Form.RecordsetClone.FindFirst "SubGroupID=" & subGrID 

Noted the following -
1.Expand the Parent. then child, click on Grandchild . error occurs as mentioned above but the form navigates to the first Child (SubGroup).
2.If I click on any child/grandchild of same parent, the code works perfectly.
3. Change the parent and click on (already expanded) grandchild, error occurs but the form navigates to the first Child (SubGroup) and related first grandchild.
I use the following event -

Private Sub TreeView0_NodeClick(ByVal Node As Object)
    Node.Selected = True
    If Node.Tag Like "G*" Or Node.Tag Like "S*" Then   ' the node is Group or SubGroup
        'Groups have tag as "G" & GroupID(PK)
        'SubGroups have tag as "S" & SubGroupID(PK)
    Else
        ShowRecord (CLng(Node.Tag))
    End If
End Sub

I missed one point though and I apologise for it. I used "On Error Resume Next" in the Sub ShowRecord and 2nd Click found the correct record.
You help is highly appreciated.

[TheSmileyCoder]

Hi Amrut

I downloaded your attachment to play around and I get the same error as you did. Initially I thought the form might be corrupt, and tried to import everything to a new db, which didn't help. I then proceeded to make a change, in moving the treeview to its own unbound form, and placing the groups form as a subform to that. This worked, and the app is now behaving as expected.

If I had to guess, its somehow related to the form itself not being done "switching" to the correct group record before trying to activate the subform, because the event of the treeviewclick is still running.

I have uploaded the working sample. TreeviewNavigation_EditByAnders.zip

I hope that helps you.

[amrut]

Thanks a lot. The db works perfectly. I appreciate your help.

[orange]

amrut,

Just curious. I see in your database in tblItems you have a field GroupFK. Presumably representing a relationship between tblGroups and tblItems. As I understood your tables, there is a hierarchy Group has 1 or more SubGroups and each SubGroup has 1 or more Items. I don't understand the reason for the GroupFK in tblItems.
It would seem to handle the case where there was no SubGroup for an Item.
Could you describe your reasoning for the field?

PS: Thanks Anders for responding and thanks to both for posting the working database with treeview.

[amrut]

orange,
I just forgot to delete it from the table.Items cannot be added without subgroup.
It was at the very initial stage of the development when the end user had asked for two tables only, Groups and Items. After further discussions, the requirement of subgroups emerged.