专家坐堂-如何实现流水帐每月底自动结算？Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

流水账是区分产品的，例如下图的库存表：

图片点击可在新窗口打开查看

此类流水账的设计方法要复杂一些，DataColChanged事件代码为：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

Select Case e.DataCol.Name
    Case "产品","入库","出库"
        Dim drs As List(of DataRow)
        Dim Filter As String
        Filter = "[_SortKey] >= " & e.DataRow("_SortKey") & " And [产品] = \'" & e.DataRow("产品") & "\'"
        drs = e.DataTable.Select(Filter)
        For Each dr As DataRow In drs
            Filter = "[_SortKey] <= " & dr("_SortKey") & " And [产品] = \'" & dr("产品") & "\'"
            Dim Val1 As Double = e.DataTable.Compute("Sum(入库)",Filter)
            Dim Val2 As Double = e.DataTable.Compute("Sum(出库)",Filter)
            dr("库存") = Val1 - Val2
        Next
        If e.DataCol.Name = "产品" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
            Filter = "[_SortKey] > " & e.DataRow("_SortKey") & " And [产品] = \'" & e.OldValue & "\'"
            drs = e.DataTable.Select(Filter)
            For Each dr As DataRow In drs
                Filter = "[_SortKey] <= " & dr("_SortKey") & " And [产品] = \'" & dr("产品") & "\'"
                Dim Val1 As Double = e.DataTable.Compute("Sum(入库)",Filter)
                Dim Val2 As Double = e.DataTable.Compute("Sum(出库)",Filter)
                dr("库存") = Val1 - Val2
            Next
        End If
End Select

如果你希望同时得到每月和截止到该月的累计，代码为：

Dim t As Table = Tables("订单")
Dim g As Subtotalgroup
t.SubtotalGroups.Clear()
\'常规统计
g = New Subtotalgroup
g.GroupOn = "日期"
g.TotalOn = "数量,金额"
t.SubtotalGroups.Add(g)
\'截止统计
g = New Subtotalgroup
g.GroupOn = "日期"
g.TotalOn = "数量,金额"
g.upto = True
t.SubtotalGroups.Add(g)
t.Subtotal()

得到的汇总模式如下：

图片点击可在新窗口打开查看

以上代码合并应用，每月和截止到该月的统计，是临时性，再操作录入时，那统计就是被取消统计，无法达到需求。