老师您好，我想在这个自动生成的月结库存表里面添加一个期初数据（就是开始做账的商品最初的期初数量和期初金额），只需要查询第一个月的 上月结存_数量=商品资料里面录入的期初库存数量，上月结存_金额=商品资料里面录入的期初库存金额，应该如何修改代码，麻烦老师指导下，谢谢
Dim s As String = e.Form.Controls("月份").Value
If s <> "" Then
Tables("表B").StopRedraw() '停止重绘
DataTables("表B").DataRows.Clear
Dim dt_in As List(Of DataRow)
dt_in = DataTables("入库表").Select("商品编号 is not null", "商品编号,商品名称,规格,月份") '排序取出 入库表 全部数据
Dim r As Row
Dim sum_curr_money, sum_prev_money As Double
Dim sum_curr_count, sum_prev_count As Integer
r = Tables("表B").AddNew
r("商品编号") = dt_in(0)("商品编号")
r("商品名称") = dt_in(0)("商品名称")
r("规格") = dt_in(0)("规格")
If dt_in(0)("月份") < s Then '之前月
sum_prev_count = sum_prev_count + dt_in(0)("数量")
sum_prev_money = sum_prev_money + dt_in(0)("金额")
Else If dt_in(0)("月份") = s Then '本月份
sum_curr_money = sum_curr_money + dt_in(0)("金额")
sum_curr_count = sum_curr_count + dt_in(0)("数量")
End If
For i As Integer = 1 To dt_in.Count - 1 '写入型号和规格,同时统计入库表数量和金额
If dt_in(i)("商品编号") <> dt_in(i-1)("商品编号") OrElse dt_in(i)("商品名称") <> dt_in(i-1)("商品名称") OrElse dt_in(i)("规格") <> dt_in(i-1)("规格") Then '不同产品
r("本月入仓_数量") = sum_curr_count
r("本月入仓_金额") = sum_curr_money
r("上月结存_数量") = sum_prev_count '暂存
r("上月结存_金额") = sum_prev_money '暂存
r = Tables("表B").AddNew '插入新数据
r("商品编号") = dt_in(i)("商品编号")
r("商品名称") = dt_in(i)("商品名称")
r("规格") = dt_in(i)("规格")
sum_curr_money = 0
sum_curr_count = 0
sum_prev_count = 0
sum_prev_money = 0
End If
If dt_in(i)("月份") < s Then '之前月
sum_prev_count = sum_prev_count + dt_in(i)("数量")
sum_prev_money = sum_prev_money + dt_in(i)("金额")
Else If dt_in(i)("月份") = s Then '本月
sum_curr_money = sum_curr_money + dt_in(i)("金额")
sum_curr_count = sum_curr_count + dt_in(i)("数量")
End If
Next
r("本月入仓_数量") = sum_curr_count
r("本月入仓_金额") = sum_curr_money
r("上月结存_数量") = sum_prev_count '暂存
r("上月结存_金额") = sum_prev_money '暂存
For Each dr As DataRow In DataTables("表B").DataRows '填充统计数据
sum_curr_count = 0 '累计本月出仓数量
sum_prev_count = 0 '累计之前出仓数量
Dim drs_out As List(Of DataRow)
Dim filter As String = "商品编号 = '" & dr("商品编号") & "' And 商品名称 = '" & dr("商品名称") & "'"
drs_out = DataTables("出库表").Select(filter, "月份") '排序取出 出仓 全部数据
For Each dr_out As DataRow In drs_out
If dr_out("月份") < s Then
sum_prev_count = sum_prev_count + dr_out("数量")
Else If dr_out("月份") = s Then
sum_curr_count = sum_curr_count + dr_out("数量")
Else
Exit For
End If
Next
Dim sum_in_prev_count As Integer = dr("上月结存_数量") '从表中取出暂存数据
Dim sum_in_prev_money As Double = dr("上月结存_金额") '从表中取出暂存数据
dr("本月出仓_数量") = sum_curr_count
dr("上月结存_数量") = sum_in_prev_count - sum_prev_count
dr("上月结存_金额") = sum_in_prev_money / sum_in_prev_count * dr("上月结存_数量")
dr("本月加权平均单价") = (dr("上月结存_金额") + dr("本月入仓_金额"))/(dr("上月结存_数量") + dr("本月入仓_数量"))
dr("本月结存_数量") = dr("上月结存_数量") + dr("本月入仓_数量") - dr("本月出仓_数量")
dr("本月结存_金额") = dr("本月加权平均单价") * dr("本月结存_数量")
dr("本月出仓_金额") = dr("本月加权平均单价") * dr("本月出仓_数量")
Next
Tables("表B").ResumeRedraw()
End If
加好友 
发短信
Post By：2020/3/29 10:36:00 [显示全部帖子]
