Option Compare Database
Option Explicit
Dim Rs(5) As ADODB.Recordset
Dim StrTemp As String
Dim iTemp As Integer

Private Sub CmdAccountSetBalance_Click()
On Error GoTo Err_CmdAccountSetBalance_Click
Dim JieF, DaiF
    IsBalance = True
    '为sglBalance赋予初值0
    For iTemp = 0 To 6
        sglBalance(iTemp) = 0
    Next iTemp
    '打开"帐薄初始化表"表
    Set Rs(0) = New ADODB.Recordset
    StrTemp = "Select * From 帐薄初始化表"
    Rs(0).Open StrTemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    '打开"试算平衡表"表
    Set Rs(1) = New ADODB.Recordset
    StrTemp = "Select * From 试算平衡表"
    Rs(1).Open StrTemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    '打开"分类表"表
    Set Rs(2) = New ADODB.Recordset
    StrTemp = "Select * From 分类表"
    Rs(2).Open StrTemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        '删除“试算平衡表”表中的记录
        Rs(1).MoveFirst
        For iTemp = 0 To Rs(1).RecordCount - 1
            Rs(1)("期初借方") = 0
            Rs(1)("期初贷方") = 0
            Rs(1)("本期发生借方") = 0
            Rs(1)("本期发生贷方") = 0
            Rs(1)("期末借方") = 0
            Rs(1)("期末贷方") = 0
            Rs(1).MoveNext
        Next iTemp
        '对应于“帐薄初始化表”表计算“试算平衡表”表的各个记录的值，并合计
        Rs(0).MoveFirst
        Rs(1).MoveFirst
        For iTemp = 0 To Rs(0).RecordCount - 1
            If IsNull(JieF) Then
                JieF = 0
            End If
            If IsNull(DaiF) Then
                DaiF = 0
            End If
            If Rs(2).RecordCount > 0 Then
                JieF = DSum("[借方]", "分类表", "[科目代码] = " & Rs(1)("科目代码"))
                DaiF = DSum("[贷方]", "分类表", "[科目代码] = " & Rs(1)("科目代码"))
                If IsNull(JieF) Then
                    JieF = 0
                End If
                If IsNull(DaiF) Then
                    DaiF = 0
                End If
            Else
                JieF = Rs(0)("累计借方")
                DaiF = Rs(0)("累计贷方")
            End If
            If Rs(2).RecordCount <= 0 Then
                If Rs(0)("余额方向") = "借方" Then
                    Rs(1)("期初借方") = Rs(0)("期初余额") - Rs(0)("累计借方")
                    Rs(1)("期初贷方") = 0 - Rs(0)("累计贷方")
                    Rs(1)("期末借方") = Rs(1)("期初借方") + JieF
                    Rs(1)("期末贷方") = Rs(1)("期初贷方") + DaiF
                Else
                    Rs(1)("期初贷方") = Rs(0)("期初余额") - Rs(0)("累计贷方")
                    Rs(1)("期初借方") = 0 - Rs(0)("累计借方")
                    Rs(1)("期末贷方") = Rs(1)("期初贷方") + DaiF
                    Rs(1)("期末借方") = Rs(1)("期初借方") + JieF
                End If
            Else
                If Rs(0)("余额方向") = "借方" Then
                    Rs(1)("期初借方") = Rs(1)("期末借方")
                    Rs(1)("期初贷方") = Rs(1)("期末贷方")
                    Rs(1)("期末借方") = Rs(1)("期初借方") + JieF
                    Rs(1)("期末贷方") = Rs(1)("期初贷方") + DaiF
                Else
                    Rs(1)("期初贷方") = Rs(1)("期末贷方")
                    Rs(1)("期初借方") = Rs(1)("期末借方")
                    Rs(1)("期末贷方") = Rs(1)("期初贷方") + DaiF
                    Rs(1)("期末借方") = Rs(1)("期初借方") + JieF
                End If
            End If
            If Rs(2).RecordCount > 0 Then
                Rs(1)("本期发生借方") = JieF
                Rs(1)("本期发生贷方") = DaiF
            Else
                Rs(1)("本期发生借方") = Rs(0)("累计借方")
                Rs(1)("本期发生贷方") = Rs(0)("累计贷方")
            End If
            '求和
            sglBalance(0) = sglBalance(0) + Rs(1)("期初借方")
            sglBalance(1) = sglBalance(1) + Rs(1)("期初贷方")
            sglBalance(2) = sglBalance(2) + Rs(1)("本期发生借方")
            sglBalance(3) = sglBalance(3) + Rs(1)("本期发生贷方")
            sglBalance(4) = sglBalance(4) + Rs(1)("期末借方")
            sglBalance(5) = sglBalance(5) + Rs(1)("期末贷方")
            Rs(0).MoveNext
            Rs(1).MoveNext
        Next iTemp
    '把求和计算结果显示于文本框内
    Me![期初借方合计].Value = sglBalance(0)
    Me![期初贷方合计] = sglBalance(1)
    Me![本期发生借方合计] = sglBalance(2)
    Me![本期发生贷方合计] = sglBalance(3)
    Me![期末借方合计] = sglBalance(4)
    Me![期末贷方合计] = sglBalance(5)
    '判断是否平衡
    If sglBalance(0) = sglBalance(1) And sglBalance(2) = sglBalance(3) _
            And sglBalance(4) = sglBalance(5) Then
        IsBalance = True
        Me![是否平衡] = "平衡"
    Else
        IsBalance = False
        Me![是否平衡] = "不平衡"
    End If
    Me![试算平衡表 子窗体].Requery
    Set Rs(0) = Nothing
    Set Rs(1) = Nothing
Exit_CmdAccountSetBalance_Click:
    Exit Sub
Err_CmdAccountSetBalance_Click:
    MsgBox Err.Description
    Resume Exit_CmdAccountSetBalance_Click
End Sub

Private Sub CmdAccountBalanceClose_Click()
On Error GoTo Err_CmdAccountBalanceClose_Click
    DoCmd.Close
Exit_CmdAccountBalanceClose_Click:
    Exit Sub
Err_CmdAccountBalanceClose_Click:
    MsgBox Err.Description
    Resume Exit_CmdAccountBalanceClose_Click
End Sub