asked on
Dim dbs As DAO.Database: Set dbs = CurrentDb
Dim rs_OuterLoop As DAO.Recordset: Dim rs_InnerLoop As DAO.Recordset
Dim TotalQuantity As Long
Set rs_OuterLoop = dbs.OpenRecordset("Select * from [tbl Transactions] where [Source] = 200 and [Destination] =1")
rs_OuterLoop.MoveFirst
Do Until rs_OuterLoop.EOF
Set rs_InnerLoop = dbs.OpenRecordset("Select * from [tbl Transactions] where [StockNum] = " & rs_OuterLoop![StockNum] & " and [Source] = 200 and [Destination] =1")
rs_InnerLoop.MoveLast
If rs_InnerLoop.RecordCount > 1 Then
' Copy entire record here. Later in the process, the record will be saved / modified (only [Quantity] will change.
TotalQuantity = 0
rs_InnerLoop.MoveFirst
Do Until rs_InnerLoop.EOF
TotalQuantity = TotalQuantity + rs_InnerLoop![Quantity]
rs_InnerLoop.MoveNext
Loop
' The 10 records are all identical... Only [Quantity] changes per record.
' I now wish to have just one record. [Quantity] = TotalQuantity
End If
'
rs_InnerLoop.Close
rs_OuterLoop.MoveNext
Loop
rs_InnerLoop.Close: Set rs_InnerLoop = Nothing
rs_OuterLoop.Close: Set rs_OuterLoop = Nothing