Access VBA Check TreeView Tree Control Child Nodes

We have a Form created that has a child control that is a TreeCtrl object, in this case, specifically a MSComctlLib.TreeCtrl.2 object. This tree control is called treeCategories and gets populated in Form_Load(). It has Checkboxes set to "Yes" in its property page, and we are going to check or uncheck the boxes downward from the node that is checked or unchecked depending on its state. We searched around for a solution that would fill this need, but found out that it wasn't out there, so we decided to make it available. Here is the code:

Option Compare Database
Option Explicit

Dim ndGlob As Node
Dim ndNext As Node

Private Sub Form_Load()
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM ItmCategories ORDER BY ParentID,Name", cn
Dim nRecs As Integer
While Not rs.EOF
If (Trim(rs.Fields("ParentID").Value) = "") Or (IsNull(rs.Fields("ParentID").Value)) Then
Me.treeCategories.Nodes.Add , , "Node " & rs.Fields("CategoryID").Value, rs.Fields("Name").Value
Me.treeCategories.Nodes("Node " & rs.Fields("CategoryID").Value).Expanded = True
Else
Me.treeCategories.Nodes.Add Me.treeCategories.Nodes("Node " & rs.Fields("ParentID").Value), tvwChild, "Node " & rs.Fields("CategoryID").Value, rs.Fields("Name").Value
Me.treeCategories.Nodes("Node " & rs.Fields("CategoryID").Value).Expanded = True
End If
rs.MoveNext
Wend
End Sub

Private Sub treeCategories_NodeCheck(ByVal Node As Object)
Dim ndd As Node
Set ndd = Me.treeCategories.Nodes(Node.Key)
Dim bCh As Boolean
bCh = ndd.Checked
If ndd.Children > 0 Then
Set ndGlob = ndd.Child
Dim nC As Integer
For nC = 1 To ndd.Children
If nC = 1 Then
Dim ndLoc1 As Node
Set ndLoc1 = CheckChild(ndGlob, bCh)
Set ndNext = ndLoc1.Next
Set ndGlob = ndNext
Else
Dim ndLoc2 As Node
Set ndLoc2 = CheckChild(ndNext, bCh)
Set ndNext = ndLoc2.Next
Set ndGlob = ndNext
End If
Next
End If
End Sub

Private Function CheckChild(ByVal ndThis As Node, bChecked As Boolean) As Node
ndThis.Checked = bChecked
If ndThis.Children > 0 Then
Set ndGlob = ndThis.Child
Dim nT As Integer
For nT = 1 To ndThis.Children
If nT = 1 Then
Dim ndLoc3 As Node
Set ndLoc3 = CheckChild(ndGlob, bChecked)
Set ndNext = ndLoc3.Next
Set ndGlob = ndNext
Else
Dim ndLoc4 As Node
Set ndLoc4 = CheckChild(ndNext, bChecked)
Set ndNext = ndLoc4.Next
Set ndGlob = ndNext
End If
Next
End If
Set CheckChild = ndThis
End Function

We had to make a couple of "bad practices" because of the scope limitations of VBA as a language, in this case the two global variables to track our position in the hierarchy of the Tree Control. Anyhow, this code also depends on a table (as you can see from the code) called ItmCategories with "ParentID," "CategoryID," and "Name" as three of the fields in the table. In our particular situation we used CurrentProject.Connection because the table exists in an external Microsoft SQL Database, in SQL Server 2005. Enjoy!