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
From novice to tech pro — start learning today.