I am implementing a Transaction Table in order to start keeping better financial records at our office. We've already had over 3000 transactions since implementing our database a little over a year ago so I'm attempting to append the price data into our Transaction Table before we start recording current transactions. I'm using 3 recordsets to get the TestID from the TestT.TestID and the AmountOwed from the TestTypeT.TestTypePrice and put them into TransactionT.TestID and TransactionT.AmountOwed. Appending the TestID is working fine but the TestTypePrice is not updating as the rsTest recordset moves to the next record. It keeps returning the price from the first record so I keep getting $45.00 on every record. What am I doing wrong here? The line with "Status" is just a textbox I use that works like a msgbox on my CodeTest Form
Code:
Dim rsTest As Recordset, rsTrans As Recordset, rsType As Recordset
Set rsTest = CurrentDb.OpenRecordset("SELECT * FROM TestT", dbOpenSnapshot)
Set rsTrans = CurrentDb.OpenRecordset("Select * FROM TransactionT", dbOpenDynaset)
Set rsType = CurrentDb.OpenRecordset("SELECT * FROM TestTypeT WHERE TestTypeID=" & rsTest!TestTypeID, dbOpenSnapshot)
On Error GoTo ERR_MSG
StatusBox = ""
Do Until rsTest.EOF
rsTrans.AddNew
rsTrans!TestID = rsTest!TestID
rsTrans!AmountOwed = rsType!TestTypePrice
Status rsTrans!TestID & " " & rsTrans!AmountOwed
rsTrans.Update
rsTest.MoveNext
Loop
KillSub:
rsType.Close
rsTrans.Close
rsTest.Close
Set rsType = Nothing
Set rsTrans = Nothing
Set rsTest = Nothing
Exit Sub
ERR_MSG:
Status "Error #" & Err.Number & " " & Err.Description
GoTo KillSub