|
- Public Sub test()
- Dim N As Long: N = 10 ' 每箱数量
- Dim L As Long ' 每箱空余数
- Dim No As Long ' 箱号号
- Dim OrderID As Long ' 订单号
- Dim RL As Long ' 当前记录剩余数
-
- ' 清表2
- CurrentProject.Connection.Execute "DELETE * FROM Table2"
-
- Dim rs As ADODB.Recordset ' 遍历记录集
-
- Dim sql As String ' SQL 语句用于查找Table1, 按订单号排序
- sql = "select * from table1 order by 订单号"
-
- Set rs = CurrentProject.Connection.Execute(sql)
-
- Do While Not rs.EOF
- If OrderID <> rs("订单号") Then
- OrderID = rs("订单号")
- No = 1
- L = N
- End If
-
- RL = rs("数量")
- Do While RL > 0
- If RL >= L Then
- ' 满一箱
- sql = "INSERT INTO Table2 (订单号, 产品, 数量, 箱号) VALUES (" & _
- OrderID & ", " & _
- "'" & rs("产品") & "', " & _
- L & ", " & _
- No & ")"
- RL = RL - L
- L = N
- No = No + 1
- Else
- ' 不满一箱
- sql = "INSERT INTO Table2 (订单号, 产品, 数量, 箱号) VALUES (" & _
- OrderID & ", " & _
- "'" & rs("产品") & "', " & _
- RL & ", " & _
- No & ")"
- L = L - RL
- RL = 0
- End If
- CurrentProject.Connection.Execute sql
- Loop
-
- rs.MoveNext
- Loop
- rs.Close
- Set rs = Nothing
- End Sub
复制代码
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?注册
x
|