WITH NodeLevel
(
   iNodeElementID, iElementID_Root, iElementID_Parent, iElementID, 
   sElementCode, sElementName
) 
AS 
(

   -- Первый корневой уровень
   SELECT 
      nd.iNodeElementID,               -- ID узла
      iElementID_Root = nd.iElementID, -- ID изделия, самого верхнего элемента
      nd.iElementID_Parent,            -- ID элемента более верхнего уровня (более крупная сборка), NULL для изделий
      nd.iElementID,                   -- ID элемента (ID самого изделия)
      nd.sElementCode,                 -- Код элемента
      nd.sElementName                  -- Название элемента
   FROM dbo.qrNodeElement nd 
   WHERE nd.iElementID_Parent IS NULL -- Условие отбора самых верхних изделий
   
   -- Все последующие уровни
   UNION ALL SELECT 
      nd.iNodeElementID,               -- ID узла
      NodeLevel.iElementID_Root,       -- ID изделия, самого верхнего элемента
      nd.iElementID_Parent,            -- ID элемента более верхнего уровня (более крупная сборка)
      nd.iElementID,                   -- ID включаемого элемента (более мелкая сборка или деталь) 
      nd.sElementCode,                 -- Код элемента
      nd.sElementName                  -- Название элемента
   FROM 
      dbo.qrNodeElement nd 
      JOIN NodeLevel ON nd.iElementID_Parent = NodeLevel.iElementID
)
SELECT 
   iNodeElementID,
   iElementID_Root,
   iElementID_Parent, 
   iElementID, 
   sElementCode, 
   sElementName
FROM NodeLevel

nNodeElementCount = ISNULL(NodeLevel.nNodeElementCount, 0) * ISNULL(nd.nNodeElementCount, 0)

ISNULL(decZehNorm, 1)

Public Sub RebuildTree()

    Dim cnn As ADODB.Connection  
    Set cnn = CurrentProject.Connection

    Dim tv As TreeView
    Dim nodeRoot As node
    Dim rsRoot As ADODB.Recordset

    Set tv = Me.ctlTree.Object
    Me.ctlTree.Visible = True
    tv.Nodes.Clear

    Set rsRoot = CurrentProject.Connection.Execute("SELECT * FROM vSostav WHERE iReplaseableElementID_Parent is Null")
        If rsRoot.EOF And rsRoot.BOF Then
          GoTo Nichts
        End If
    While Not rsRoot.EOF
    Set nodeRoot = tv.Nodes.Add( _
        Key:="ROOT" & CStr(rsRoot!iNodeElementID), _
        Text:=rsRoot!strElementName & " " & rsRoot!strElementDescription & " " & rsRoot!strElementStandart)
      nodeRoot.Tag = rsRoot!iNodeElementID
      nodeRoot.Expanded = True
      Call LoadLevel(tv, nodeRoot, rsRoot!iElementID)
      rsRoot.MoveNext
    Wend
      
    'Call TreeView_NodeClick(nodeRoot)
Nichts:
    rsRoot.Close: Set rsRoot = Nothing
    Me.ctlTree.SetFocus

End Sub

Public Function LoadLevel(tv, nodeParent, iElementID)

    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Set rs = CurrentProject.Connection.Execute("SELECT * FROM vSostav WHERE iReplaseableElementID_Parent= " & iElementID & _
    "Order by sngGroupOfEShifr, strPodGroupOfEName, strElementName, strElementDescription")
    Dim nodeGroup As node
    Dim node As node
    While Not rs.EOF
    If rs!sngGroupOfEShifr = 10 Then
    Set nodeGroup = tv.Nodes.Add( _
        relative:=nodeParent.Index, relationship:=tvwChild, _
        Key:="NODE" & nodeParent & "Изделия", _
        Text:="Изделия")
      While rs!sngGroupOfEShifr = 10
         Set node = tv.Nodes.Add( _
            relative:=nodeGroup.Index, relationship:=tvwChild, _
            Key:="NODE" & nodeParent & CStr(rs!iNodeElementID), _
            Text:=rs!strElementName & " " & rs!strElementDescription & " " & rs!strElementStandart & " " & CStr(rs!iNodeElementID))
        node.Tag = rs!iNodeElementID
        node.Expanded = True
        Call LoadLevel(tv, node, rs!iElementID)
        rs.MoveNext
        If rs.EOF Then GoTo Exit_1
        Wend
Exit_1:
        
    ElseIf rs!sngGroupOfEShifr = 10.1 Then
    Set nodeGroup = tv.Nodes.Add( _
        relative:=nodeParent.Index, relationship:=tvwChild, _
        Key:="NODE" & nodeParent & "Материалы", _
        Text:="Материалы")
      While rs.EOF = False And rs!sngGroupOfEShifr = 10.1
         Set node = tv.Nodes.Add( _
            relative:=nodeGroup.Index, relationship:=tvwChild, _
            Key:="NODE" & nodeParent & CStr(rs!iNodeElementID), _
            Text:=rs!strElementName & " " & rs!strElementDescription & " " & rs!strElementStandart) ' & CStr(rs!iNodeElementID))
        node.Tag = rs!iNodeElementID
        node.Expanded = True
        Call LoadLevel(tv, node, rs!iElementID)
        rs.MoveNext
        If rs.EOF Then GoTo Exit_2
        Wend
Exit_2:
            
     ElseIf rs!sngGroupOfEShifr = 10.3 Then
     Set nodeGroup = tv.Nodes.Add( _
        relative:=nodeParent.Index, relationship:=tvwChild, _
        Key:="NODE" & nodeParent & "ГСМ", _
        Text:="ГСМ")
      While rs.EOF = False And rs!sngGroupOfEShifr = 10.3
         Set node = tv.Nodes.Add( _
            relative:=nodeGroup.Index, relationship:=tvwChild, _
            Key:="NODE" & nodeParent & CStr(rs!iNodeElementID), _
            Text:=rs!strElementName & " " & rs!strElementDescription & " " & rs!strElementStandart) ' & CStr(rs!iNodeElementID))
        node.Tag = rs!iNodeElementID
        node.Expanded = True
        Call LoadLevel(tv, node, rs!iElementID)
        rs.MoveNext
        If rs.EOF Then GoTo Exit_3
        Wend
Exit_3:

     ElseIf rs!sngGroupOfEShifr = 10.5 Then
     Set nodeGroup = tv.Nodes.Add( _
        relative:=nodeParent.Index, relationship:=tvwChild, _
        Key:="NODE" & nodeParent & "Запчасти", _
        Text:="Запчасти")
      While rs.EOF = False And rs!sngGroupOfEShifr = 10.5
         Set node = tv.Nodes.Add( _
            relative:=nodeGroup.Index, relationship:=tvwChild, _
            Key:="NODE" & nodeParent & CStr(rs!iNodeElementID), _
            Text:=rs!strElementName & " " & rs!strElementDescription & " " & rs!strElementStandart) ' & CStr(rs!iNodeElementID))
        node.Tag = rs!iNodeElementID
        node.Expanded = True
        Call LoadLevel(tv, node, rs!iElementID)
        rs.MoveNext
        If rs.EOF Then GoTo Exit_4
        Wend
Exit_4:
      
     ElseIf rs.EOF = False Then
     Set nodeGroup = tv.Nodes.Add( _
        relative:=nodeParent.Index, relationship:=tvwChild, _
        Key:="NODE" & nodeParent & "Неопределено", _
        Text:="Неопределено")
      While rs.EOF = False
         Set node = tv.Nodes.Add( _
            relative:=nodeGroup.Index, relationship:=tvwChild, _
            Key:="NODE" & nodeParent & CStr(rs!iNodeElementID), _
            Text:=rs!strElementName & " " & rs!strElementDescription & " " & rs!strElementStandart) ' & CStr(rs!iNodeElementID))
        node.Tag = rs!iNodeElementID
        node.Expanded = True
        Call LoadLevel(tv, node, rs!iElementID)
        rs.MoveNext
        If rs.EOF Then GoTo Exit_5
        Wend
Exit_5:
     End If
     Wend
        
    
End Function

K e y : = " N O D E " & n o d e P a r e n t & C S t r ( r s ! i N o d e E l eme n t I D ) , _ T e x t : = r s ! s t r E l eme n t N ame & " " & r s ! s t r E l eme n t D e s c r i p t i o n & " " & r s ! s t r E l eme n t S t a n d a r t & " " & C S t r ( r s ! i N o d e E l eme n t I D ) )

WITH NodeLevel  
(
iNodeElementID, iElementID_Root, iElementID_Parent, iElementID, decNodeElementCnt, decNodeElementNormSum, decNodeElementNormObosn, iPodrasdelID, iPodrasdel3ID, 
strPodrasdel, strPodrasdel3, strPrim, strObosnovanie, strDopObosnovanie, strVid, decNodeElementCntSum
)
 AS 
(
SELECT     
                  iNodeElementID, 
                  iReplaseableElementID AS iElementID_Root, 
                  iReplaseableElementID_Parent AS iElementID_Parent, 
                  iReplaseableElementID AS iElementID,  
                  decNodeElementCnt, 
                  decNodeElementNormSum, 
                  decNodeElementNormObosn, 
                  iPodrasdelID, 
                  iPodrasdel3ID, 
                  strPodrasdel, 
                  strPodrasdel3, 
                  strPrim, 
                  strObosnovanie, 
                  strDopObosnovanie, 
                  strVid,
                  Cast(ISNULL (nd.decNodeElementCnt, 0) as decimal (8,3))
FROM  dbo.tblNodeElement AS nd
WHERE     (iReplaseableElementID_Parent IS NULL)
UNION ALL SELECT     
                  nd.iNodeElementID, 
                  NodeLevel.iElementID_Root, 
                  nd.iReplaseableElementID_Parent,
                  nd.iReplaseableElementID, 
                  nd.decNodeElementCnt, 
                  nd.decNodeElementNormSum, 
                  nd.decNodeElementNormObosn, 
                  nd.iPodrasdelID, 
                  nd.iPodrasdel3ID, 
                  nd.strPodrasdel, 
                  nd.strPodrasdel3, 
                  nd.strPrim, 
                  nd.strObosnovanie, 
                  nd.strDopObosnovanie, 
                  nd.strVid,
                  decNodeElementCntSum = Cast((ISNULL (NodeLevel.decNodeElementCnt, 0) * ISNULL (nd.decNodeElementCnt, 0)) as decimal (8,3)) 
FROM dbo.tblNodeElement AS nd
           JOIN NodeLevel ON nd.iReplaseableElementID_Parent = NodeLevel.iElementID
)
SELECT
                  iNodeElementID, 
                  iElementID_Root, 
                  iElementID_Parent, 
                  iElementID,  
                  decNodeElementCnt, 
                  decNodeElementNormSum, 
                  decNodeElementNormObosn, 
                  iPodrasdelID, 
                  iPodrasdel3ID, 
                  strPodrasdel, 
                  strPodrasdel3, 
                  strPrim, 
                  strObosnovanie, 
                  strDopObosnovanie, 
                  strVid,
                  decNodeElementCntSum
FROM NodeLevel