专家坐堂-[求助]流水账不能自动计算Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  [求助]流水账不能自动计算  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=163341)

--  作者：huaziqiao1976
--  发布时间：2021/4/23 22:43:00
--  [求助]流水账不能自动计算
计算的代码如下
Select Case e.DataCol.Name
Case "物资名称","入库","出库"
Dim drs As List(of DataRow)
Dim Filter As String
Filter = "[_SortKey] >= " & e.DataRow("_SortKey") & " And [物资名称] = \'" & e.DataRow("物资名称") & "\'And [规格型号] = \'" & e.DataRow("规格型号") & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[_SortKey] <= " & dr("_SortKey") & " And [物资名称] = \'" & dr("物资名称") & "\'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 & "\'And [规格型号] = \'" & e.OldValue & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[_SortKey] <= " & dr("_SortKey") & " And [物资名称] = \'" & dr("物资名称") & "\' 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 f As New Filler
f.SourceTable = DataTables("销售明细") \'指定数据来源
f.SourceCols = "单号,物资名称,规格型号,购货方,销售日期,年度,数量" \'指定数据来源列
f.DataTable = DataTables("库存明细表") \'指定数据接收表
f.DataCols = "购货编号,物资名称,规格型号,购货方,日期,年度,出库" \'指定数据接收列
f.Fill() \'填充数据
DataTables("库存明细表").Save()
数据填充过来后不会自动计算库存,如果数据填充在出库,就得手动在入库输个0才能得出余额
求指导

--  作者：有点蓝
--  发布时间：2021/4/24 9:33:00
--

Select Case e.DataCol.Name
Case "物资名称","入库","出库"，"规格型号"
Dim drs As List(of DataRow)
Dim Filter As String
Filter = "[_SortKey] >= " & e.DataRow("_SortKey") & " And [物资名称] = \'" & e.DataRow("物资名称") & "\'And [规格型号] = \'" & e.DataRow("规格型号") & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[_SortKey] <= " & dr("_SortKey") & " And [物资名称] = \'" & dr("物资名称") & "\'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 & "\'And [规格型号] = \'" & e.datarow("规格型号") & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[_SortKey] <= " & dr("_SortKey") & " And [物资名称] = \'" & dr("物资名称") & "\' 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
elseIf e.DataCol.Name = "规格型号" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
Filter = "[_SortKey] > " & e.DataRow("_SortKey") & " And [物资名称] = \'" & e.datarow("物资名称") & "\' And [规格型号] = \'" & e.OldValue & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[_SortKey] <= " & dr("_SortKey") & " And [物资名称] = \'" & dr("物资名称") & "\' 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

这种填充的，建议填充完毕后再调用一下重置列进行计算，因为填充是按单元格写入数据的，在写入物资名称的时候，出入库还没有数据，会导致多余的计算，或者计算不准确

[此贴子已经被作者于2021/4/24 9:34:52编辑过]

--  作者：huaziqiao1976
--  发布时间：2021/4/24 14:22:00
--
物资名称相同,型号不同的可以计算了,但是名称不同,规格相同的不能计算,
[此贴子已经被作者于2021/4/24 14:23:06编辑过]

--  作者：huaziqiao1976
--  发布时间：2021/4/24 14:24:00
--
发图

--  作者：huaziqiao1976
--  发布时间：2021/4/24 14:25:00
--
发图

--  作者：有点蓝
--  发布时间：2021/4/24 14:37:00
--
elseIf改为if

--  作者：huaziqiao1976
--  发布时间：2021/4/24 18:33:00
--
解决了,是刷新的问题,要刷新"物资名称"和"规格型号"两例,刷新的代码有没有好的写法
Dim nms As New List(Of String)
Dim drs As New List(of DataRow)
With DataTables("库存明细表")
nms = .GetValues("物资名称")
For Each nm As String In nms \'找出每个产品的第一行数据, 添加到集合drs中
drs.Add(.Find("物资名称 = \'" & nm & "\'", "[_SortKey]"))
Next
For Each dr As DataRow In drs
.DataCols("入库").RaiseDataColChanged(dr)
Next
End With

Dim nms1 As New List(Of String)
Dim drs1 As New List(of DataRow)
With DataTables("库存明细表")
nms1 = .GetValues("规格型号")
For Each nm1 As String In nms1 \'找出每个产品的第一行数据, 添加到集合drs中
drs1.Add(.Find("规格型号 = \'" & nm1 & "\'", "[_SortKey]"))
Next
For Each dr1 As DataRow In drs1
.DataCols("入库").RaiseDataColChanged(dr1)
Next
End With

--  作者：有点蓝
--  发布时间：2021/4/25 8:50:00
--
case 里如果同时有物资名称和规格型号，同时表格数据也同时有物资名称和规格型号数据，重置其中一个列即可，否则只能像上面这样处理了