两张表结构如下:
表1:
Material_Code Material_Name
0000001 辟刀
0000002 塑胶
0000003 电容
0000004 锂电池
0000005 三项开关
0000006 悬切机
0000007 .
0000008 .
0000009 .
0000010 .
. .
表2:
Parent_Code Component_Code
0000001 0000002
0000001 0000005
0000004 0000003
0000004 0000002
0000006 0000001
0000006 0000004
0000006 0000005
0000002 0000007
功能要求:把 Material_Code的内容加载到treeview中, 如果Material_Code等于表2的 Parent_Code,
则把parent_Code对应的component_Code作为Material_Code的子节点,如果该component_Code出现在Parent_Code中,
则把该Parent_Code对应的component_Code,作为下一级子节点加载上来。(类似bom的多级展开) 例如:
root
--0000001
--0000002
--0000007
--0000005
--0000002
--0000007
--0000003
--0000004
--0000003
--0000002
--0000007
--0000005
--0000006
--0000001
--0000002
--0000005
--0000004
--0000003
--0000002
--0000005
我的程序如下:可能是递归出了问题,请帮忙啊!!!
Private Function Bln_Load_TreeNodes() As Boolean
Dim i As Integer, j As Integer
Parent_CodeQuery '函数Parent_CodeQuery 提取 Component_Code
Material_CodeQuery '函数Material_CodeQuery从 表 Material_BaseData中 提取 Material_Code,Material_Name
If Not rs2.EOF Then
r = 1
For i = 1 To rs2.RecordCount
NodeText = Trim(rs2!Material_Code)
strKey = "Key" & CStr(r)
Set SonNode1 = TreeView1.Nodes.Add("Root", tvwChild, strKey, NodeText)
SonNode1.EnsureVisible
r = r + 1
Bln_Add_SonNode SonNode1, NodeText
rs2.MoveNext
rs1.MoveFirst
Next
Else
End If
End Function
'Bln_Add_SonNode递归函数
Private Function Bln_Add_SonNode(NowNode As Node, strNode As String) As Boolean
Dim nodeItem As String
If Not rs1.EOF Then
nodeItem = Trim(rs1!Parent_Code) '子件代码
NodeText1 = Trim(rs1!Component_Code)
If nodeItem = strNode Then
r = r + 1
StrKey1 = "MyKey" & CStr(r)
Set SonNode2 = TreeView1.Nodes.Add(strKey, tvwChild, StrKey1, NodeText1)
SonNode2.EnsureVisible
Else
Set SonNode2 = NowNode
End If
rs1.MoveNext
Bln_Add_SonNode SonNode2, NodeText1
End If
End Function
表1:
Material_Code Material_Name
0000001 辟刀
0000002 塑胶
0000003 电容
0000004 锂电池
0000005 三项开关
0000006 悬切机
0000007 .
0000008 .
0000009 .
0000010 .
. .
表2:
Parent_Code Component_Code
0000001 0000002
0000001 0000005
0000004 0000003
0000004 0000002
0000006 0000001
0000006 0000004
0000006 0000005
0000002 0000007
功能要求:把 Material_Code的内容加载到treeview中, 如果Material_Code等于表2的 Parent_Code,
则把parent_Code对应的component_Code作为Material_Code的子节点,如果该component_Code出现在Parent_Code中,
则把该Parent_Code对应的component_Code,作为下一级子节点加载上来。(类似bom的多级展开) 例如:
root
--0000001
--0000002
--0000007
--0000005
--0000002
--0000007
--0000003
--0000004
--0000003
--0000002
--0000007
--0000005
--0000006
--0000001
--0000002
--0000005
--0000004
--0000003
--0000002
--0000005
我的程序如下:可能是递归出了问题,请帮忙啊!!!
Private Function Bln_Load_TreeNodes() As Boolean
Dim i As Integer, j As Integer
Parent_CodeQuery '函数Parent_CodeQuery 提取 Component_Code
Material_CodeQuery '函数Material_CodeQuery从 表 Material_BaseData中 提取 Material_Code,Material_Name
If Not rs2.EOF Then
r = 1
For i = 1 To rs2.RecordCount
NodeText = Trim(rs2!Material_Code)
strKey = "Key" & CStr(r)
Set SonNode1 = TreeView1.Nodes.Add("Root", tvwChild, strKey, NodeText)
SonNode1.EnsureVisible
r = r + 1
Bln_Add_SonNode SonNode1, NodeText
rs2.MoveNext
rs1.MoveFirst
Next
Else
End If
End Function
'Bln_Add_SonNode递归函数
Private Function Bln_Add_SonNode(NowNode As Node, strNode As String) As Boolean
Dim nodeItem As String
If Not rs1.EOF Then
nodeItem = Trim(rs1!Parent_Code) '子件代码
NodeText1 = Trim(rs1!Component_Code)
If nodeItem = strNode Then
r = r + 1
StrKey1 = "MyKey" & CStr(r)
Set SonNode2 = TreeView1.Nodes.Add(strKey, tvwChild, StrKey1, NodeText1)
SonNode2.EnsureVisible
Else
Set SonNode2 = NowNode
End If
rs1.MoveNext
Bln_Add_SonNode SonNode2, NodeText1
End If
End Function
然后再装表2中的内容,这样写:
Set xNode = .Nodes.Add("r"&Parent_Code, tvwChild, "L"Component_Code&, 显示内容)我的MSN:[email protected]
Function Bln_Add_SonNode(NowNode As Node, strNode As String,iRelation as integer)父节点添加时用参数tvwChild,同一级别的节点添加时用参数tvwNext,上面的调用改成:
Set SonNode2 = TreeView1.Nodes.Add(strKey, iRelation , StrKey1, NodeText1)
Set xNode = tvw.Nodes.Add("r"&Parent_Code, tvwChild, "L"&Component_Code&, 显示内容)
这并不是问题的关键,关键是怎样理顺节点之间的关系,期待中、、、、、、
Select * From 表2 Order By Node_layer然后按顺序添加就行了,这样做的好处是可以保证每个节点的父节点都已被添加之后再添加该节点,我就是这么实现的。
Option Explicit
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
''''''''''''''''''''''''''''''''''''''''
Dim X, BomNode As Node
Dim Arr() As String '纪录选中的代码
Dim j As Integer '数组下标
Dim strStatus As Boolean
Dim Parent_Code As String
''''''''''''''''''''''''''''''''''''
Private Sub Form_Load()
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Call TreeINI
End Sub'函数Material_CodeQuery从 表 Material_BaseData中 提取 Material_Code,Material_Name
Private Function Material_CodeQuery() As Boolean
Call Pr_EPD_DBConn ' open database
With cmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "Pro_Select_Material_Code_V_EPD_Material_Insert"
.Execute
End With
With rs2
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.Open cmd
End With
End FunctionPublic Function TreeINI()
' On Error GoTo ErrTreeINI
Dim strProductCode, strProductName As String
trcBom.Nodes.Clear
Set BomNode = Me.trcBom.Nodes.Add(, , "root", "产品结构")
BomNode.Tag = "Root"
Material_CodeQuery '函数Material_CodeQuery从 表 Material_BaseData中 提取 Material_Code,Material_Name
rs2.MoveFirst
While Not rs2.EOF
strProductCode = Trim(rs2!Material_Code)
strProductName = Trim(rs2!Material_Name)
Set BomNode = Me.trcBom.Nodes.Add("root", 4, Trim("R." & strProductCode), Trim(strProductCode) & " " & Trim(strProductName))
BomNode.Sorted = True
BomNode.Tag = Trim("R." & strProductCode)
If DCount(strProductCode) <> 0 Then
Set BomNode = Me.trcBom.Nodes.Add(BomNode.Tag, 4, , "")
End If
rs2.MoveNext
Wend
For Each X In Me.trcBom.Nodes
If X.Tag = "root" Then
X.Expanded = True
Exit Function
End If
Next X
Exit Function
ErrTreeINI:
MsgBox Err.Description, vbCritical, "错误"
Exit Function
End Function
Private Sub trcBom_Expand(ByVal Node As ComctlLib.Node)
If Node.Tag = "Root" Then Exit Sub
trcBom.Nodes(Node.Tag).Selected = True
Call BomExpand(Node)
End SubPrivate Function BomExpand(Node As Node)
On Error GoTo ErrBomExpend
Dim strCode As String, strName As String, strParCode As String
Dim strID As String
Dim i As Integer
For i = 1 To Node.Children
Me.trcBom.Nodes.Remove (Node.Child.Index)
Next
strParCode = GetParCode(Node.Tag)
If ComQuery(strParCode) = 0 Then Exit Function
rs2.MoveFirst
While Not rs2.EOF
strCode = Trim(rs2!Component_Code)
strName = DLookup(strCode)
strID = Trim(Node.Tag & "." & strCode)
Set BomNode = Me.trcBom.Nodes.Add(Node.Tag, 4, strID, Trim(strCode) & " " & Trim(strName))
BomNode.Sorted = True
BomNode.Tag = strID
If DCount(strCode) <> 0 Then '是否有子节点
Set BomNode = Me.trcBom.Nodes.Add(BomNode.Tag, 4, , "")
End If
rs2.MoveNext
Wend
Exit Function
ErrBomExpend:
MsgBox Err.Description, vbCritical, "ERROR"
End Function
Public Function GetParCode(Code As String) As String
Dim i As Integer
Dim MyCode As String
GetParCode = ""
MyCode = Code
For i = Len(Code) To 1 Step -1
MyCode = Right(MyCode, 1)
If MyCode = "." Then
GetParCode = GetParCode
Exit For
Else
GetParCode = MyCode & GetParCode
MyCode = Left(Code, i - 1)
End If
Next
End Function
Private Function DCount(ByVal Parent_Code As String) As Integer
Call Pr_EPD_DBConn ' open database
With cmd
.ActiveConnection = conn
.CommandType = adCmdText
.CommandText = "select * from T_EPD_Bom where Parent_Code='" & Parent_Code & "' "
.Execute
End With
Set rs1 = New ADODB.Recordset
With rs1
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.Open cmd
If Not .EOF Then
DCount = 1
rs1.Close
Exit Function
End If
DCount = 0
End With
End Function
Private Function ComQuery(ByVal Parent_Code As String) As Integer
Call Pr_EPD_DBConn ' open database
With cmd
.ActiveConnection = conn
.CommandType = adCmdText
.CommandText = "SELECT component_code FROM T_epd_bom WHERE parent_code = '" & Parent_Code & "'"
.Execute
End With
Set rs2 = New ADODB.Recordset
With rs2
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.Open cmd
If Not .EOF Then
ComQuery = 1
Exit Function
End If
ComQuery = 0
End With
End Function
Private Function DLookup(ByVal strParCode As String) As String
Call Pr_EPD_DBConn ' open database
With cmd
.ActiveConnection = conn
.CommandType = adCmdText
.CommandText = "SELECT Material_Name FROM V_EPD_Material_Insert WHERE Material_code = '" & strParCode & "'"
.Execute
End With
Set rs1 = New ADODB.Recordset
With rs1
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.Open cmd
If Not .EOF Then
DLookup = .Fields("Material_Name").Value
End If
End With
End Function