主题：计算库存错误码

 

成品库存计算:

成品出入库有9种出入库明细,每种表中都有：产品号、类别、商标、品名、规格、包装、件数这几个字段，“成品期末库”表中也有这几个字段。

Vars("jsrq")为截止日期，是一个变量

“成品期末库”中DataColChanged的事件:

If e.DataCol.Name = "产品号" Then

    Dim Filter As String = " [产品号] = '" & e.DataRow("产品号") & "' And [日期] <= #" & Vars("jsrq") & "# "

   

    e.DataRow("成品件数") = DataTables("成品期初库存").Compute("Sum(件数)"," [产品号] = '" & e.DataRow("产品号") & "'") _

    + DataTables("成品入库单明细").Compute("Sum(件数)", Filter) _

    + DataTables("外购成品入库单明细").Compute("Sum(件数)", Filter) _

    + DataTables("衬塑入库单明细").Compute("Sum(件数)", Filter) _

    + DataTables("冷镀入库单明细").Compute("Sum(件数)", Filter) _

    + DataTables("零个转整入库单明细").Compute("Sum(件数)", Filter) _

    + DataTables("成品调整单明细").Compute("Sum(件数)", Filter) _

    - DataTables("成品销单明细").Compute("Sum(件数)", Filter) _

    - DataTables("衬塑出库单明细").Compute("Sum(件数)", Filter) _

    - DataTables("冷镀出库单明细").Compute("Sum(件数)", Filter)

   

End If

窗体按钮代码如下：

 

If MainTable.Name = "成品期末库存" Then

    Dim f As New Filler

    DataTables("成品期末库存").DataRows.Clear() '清除原有数据

    f.SourceTable = DataTables("产品表")

    f.SourceCols = "产品号,类别,商标,品名,规格,包装"

    f.DataTable = DataTables("成品期末库存")

    f.DataCols = "产品号,类别,商标,品名,规格,包装"

    f.Filter = ("[产品号] <> """)

    f.Fill()

End If

 

  DataTables("成品期末库存").DataCols("产品号").RaiseDataColChanged() 

运行需10多分钟才能算出，产品号有2000左右，数据表有20万行左右

这段代码应该是日期比较的问题，要换一种思路

 

http://www.foxtable.com/help/topics/2219.htm

 

Dim Filter As String = " [产品号] = '" & e.DataRow("产品号") & "'"
Dim sum As Double = 0
Dim dts() As String = {"成品期初库存", "成品入库单明细"}
For Each dt As String In dts
    For Each dr As DataRow In DataTables(dt).Select("", "日期")
        If dr("日期") > Vars("jsrq") Then Exit For
        sum = sum + dr("件数")
    Next
Next

Dim sum2 As Double = 0
Dim dts2() As String = {"成品销单明细"}
For Each dt As String In dts2
    For Each dr As DataRow In DataTables(dt).Select("", "日期")
        If dr("日期") > Vars("jsrq") Then Exit For
        sum = sum + dr("件数")
    Next
Next

e.DataRow("成品件数") = sum - sum2

If e.DataCol.Name = "产品号" Then
       Dim Filter As String = " [产品号] = '" & e.DataRow("产品号") & "'"
    Dim sum As Double = 0
    Dim dts() As String = {"成品期初库存", "成品入库单明细","外购成品入库单明细","衬塑入库单明细","零个转整入库单明细","冷镀入库单明细"}
    For Each dt As String In dts
        For Each dr As DataRow In DataTables(dt).Select(Filter, "日期")
            If dr("日期") > Vars("jsrq") Then Exit For
            sum = sum + dr("件数")
        Next
    Next
   
    Dim sum2 As Double = 0
    Dim dts2() As String = {"成品销单明细","衬塑出库单明细","冷镀出库单明细"}
    For Each dt As String In dts2
        For Each dr As DataRow In DataTables(dt).Select( Filter , "日期")
            If dr("日期") > Vars("jsrq") Then Exit For
            sum = sum + dr("件数")
        Next
    Next
   
   
    e.DataRow("成品件数") = sum - sum2
End If

谢谢，可达秒级了

这里写错了!!!

 

sum2 = sum2 + dr("件数")