|
本帖最后由 Henry D. Sy 于 2011-12-22 12:06 编辑
建议在b中增加一字段:分配后余额,用于显示实际库存
则代码调整如下:- Private Sub Command0_Click()
- Dim rs As New ADODB.Recordset
- Dim rst As New ADODB.Recordset
- Dim cnn As New ADODB.Connection
- Dim sSQL As String
- Dim lngBal As Long
- Dim lngUsed As Long
- Set cnn = CurrentProject.Connection
- sSQL = "select 类别,剩余数量,分配后余额 from b"
- rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic
- Do While Not rs.EOF
- lngBal = rs.Fields("剩余数量")
- lngUsed = 0
- sSQL = "select * from a where 类别='" & rs.Fields("类别") & "'"
- rst.Open sSQL, cnn, adOpenKeyset, adLockOptimistic
- With rst
- Do While Not .EOF
- If lngBal >= .Fields("需求数额") Then
- .Fields("配额") = .Fields("需求数额")
- Else
- .Fields("配额") = lngBal
- End If
- .Fields("差额") = .Fields("配额") - .Fields("需求数额")
- lngBal = lngBal - .Fields("配额")
- lngUsed = lngUsed + .Fields("配额")
- .Update
- .MoveNext
- Loop
- .Close
- End With
- rs.Fields("分配后余额") = rs.Fields("剩余数量") - lngUsed
- rs.Update
- rs.MoveNext
- Loop
- rs.Close
- Set rs = Nothing
- Set rst = Nothing
- Set cnn = Nothing
- End Sub
复制代码 |
|