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
Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.