7,714
社区成员
发帖
与我相关
我的任务
分享
'算从lngrootid这个根节点开始的所有子节点(含根节点)的金额和百分比
'并把计算结果放在节点表的相应字段里
'返回根节点的值
Public Function CalValuePercent(strNodeTable As String, lngRootId As Long) As Single
On Error GoTo Err_CalValuePercent
Dim rstNode As DAO.Recordset, rstDetail As DAO.Recordset
Dim strSql As String
Dim sngValue As Single
Dim blnNoChild As Boolean
sngValue = 0
'得到每个孩子的值(递归)并累积自己的值(等于所有孩子的值的总和)
strSql = "SELECT lngNodeId FROM " & strNodeTable & " WHERE lngFatherId = " & lngRootId
Set rstNode = CurrentDb.OpenRecordset(strSql)
If rstNode.EOF Then
blnNoChild = True
Else
blnNoChild = False
While Not rstNode.EOF
sngValue = sngValue + CalValuePercent(strNodeTable, rstNode.Fields("lngNodeId")) '递归调用
rstNode.MoveNext
Wend
End If
rstNode.Close
Set rstNode = Nothing
'将自己的值记录到数据库里
If blnNoChild Then
strSql = "SELECT strDetailTable, lngDetailId FROM " & strNodeTable & " WHERE lngNodeId = " & lngRootId
Set rstNode = CurrentDb.OpenRecordset(strSql)
If IsBasicTable3Db(Nz(rstNode.Fields("strDetailTable"))) Then '如果是基础节点,则从基础表抄value过来
strSql = "SELECT sngValue FROM " & rstNode.Fields("strDetailTable") & " WHERE lngId = " & rstNode.Fields("lngDetailId")
Set rstDetail = CurrentDb.OpenRecordset(strSql)
sngValue = Nz(rstDetail.Fields("sngValue"))
rstDetail.Close
Set rstDetail = Nothing
End If
rstNode.Close
Set rstNode = Nothing
End If
strSql = "UPDATE [" & strNodeTable & "] SET [sngValue] = " & sngValue & " WHERE lngNodeId = " & lngRootId
CurrentDb.Execute strSql
' strSql = "SELECT lngNodeId, strDetailTable, lngDetailId, sngValue FROM " & strNodeTable & " WHERE lngNodeId = " & lngRootId
' Set rstNode = CurrentDb.OpenRecordset(strSql)
' If blnNoChild And IsBasicTable3Db(Nz(rstNode.Fields("strDetailTable"))) Then '如果是基础节点,则从基础表抄value过来
' strSql = "SELECT sngValue FROM " & rstNode.Fields("strDetailTable") & " WHERE lngId = " & rstNode.Fields("lngDetailId")
' Set rstDetail = CurrentDb.OpenRecordset(strSql)
' sngValue = Nz(rstDetail.Fields("sngValue"))
' rstDetail.Close
' Set rstDetail = Nothing
' End If
' rstNode.Edit
' rstNode.Fields("sngValue") = sngValue
' rstNode.Update
' rstNode.Close
' Set rstNode = Nothing
'计算每个孩子的百分比
If Not blnNoChild Then
strSql = "UPDATE [" & strNodeTable & "] SET [sngPercent] = " & _
"IIF(" & sngValue & "=0, 0, [sngValue]/" & sngValue & ") " & _
"WHERE [lngFatherId]=" & lngRootId
CurrentDb.Execute strSql
' strSql = "SELECT lngNodeId, sngValue, sngPercent FROM " & strNodeTable & " WHERE lngFatherId = " & lngRootId
' Set rstNode = CurrentDb.OpenRecordset(strSql)
' While Not rstNode.EOF
' rstNode.Edit
' If sngValue = 0 Then
' rstNode.Fields("sngPercent") = 0
' Else
' rstNode.Fields("sngPercent") = rstNode.Fields("sngValue") / sngValue
' End If
' rstNode.Update
' rstNode.MoveNext
' Wend
' rstNode.Close
' Set rstNode = Nothing
End If
CalValuePercent = sngValue
Exit Function
Err_CalValuePercent:
Stop
Debug.Print ERR.Description
Resume
End Function