-----Original Message-----
I have a DAO recordset (rst) based on a query in a nested
loop. The first loop provides the parameters for the
recordset which is then Set in the second loop.
The first time thru all works fine. When I reach EOF on
the recordset I rst.Close and Set rst = nothing.
The first loop then sets the new parameters for the rst
query. I perform the second loop again with a set rst=
(query). Then when the code executes rst.MoveFirst I get
the error "No current record".
How can I reset the recordset so I can re set it and work
with the new records.
Thank You
.
HERE IS THE CODE
Dim rstC As DAO.Recordset
Dim rstF As DAO.Recordset
Set rstC = CurrentDb.OpenRecordset("SELECT * FROM
tbl_Credits WHERE (tbl_Credits.DevID) = " & intDev _
& " ORDER BY tbl_Credits.RecID,
tbl_Credits.FeeDate")
rstC.MoveFirst
curAmount = rstC!RAmount
Do While (Not rstC.EOF)
Set rstF = CurrentDb.OpenRecordset("SELECT * FROM
tbl_Charges WHERE (tbl_Charges.DevID) = " & intDev _
& " And (tbl_Charges.RecID) = " & rstC!RecID
& " And (tbl_Charges.Balance) > 0" _
& " ORDER BY tbl_Charges.FeeType = " &
strFee1 & ", tbl_Charges.FeeType = " & strFee2 _
& ", tbl_Charges.FeeType = " & strFee3 & ",
tbl_Charges.FeeType = " & strFee4 _
& ", tbl_Charges.FeeType = " & strFee5 & ",
tbl_Charges.FeeType = " & strFee6 _
& ", tbl_Charges.FeeType = " & strFee7 & ",
tbl_Charges.FeeType = " & strFee8 _
& ", tbl_Charges.FeeType = " & strFee9 & ",
tbl_Charges.FeeType = " & strFee0 _
& ", tbl_Charges.FeeDate")
rstF.MoveFirst
curAmount = rstC!RAmount
Do While (Not rstF.EOF) And curAmount > 0
ProcessRec:
If rstF!Balance <= curAmount Then
curAmount = curAmount - rstF!Balance
strInsert = "INSERT INTO tbl_Recipts " _
& "(DevID, RecID, RType, RDate,
RAmount, Check, Account) VALUES " _
& "(" & rstF!DevId & ", " & rstF!
RecID & ", '" & rstF!FeeType & "', #" & rstC!RDate
& "#, " & rstF!Balance & ", '" & rstC!Check & "', " &
rstF!ContraAcct & ");"
DoCmd.RunSQL strInsert
strUpdate = "UPDATE tbl_Charges SET
tbl_Charges.Balance = 0 WHERE tbl_Charges.TMStamp = #" &
rstC!CTM & "# AND tbl_Charges.RecID = " _
& rstC!RecID & " and FeeType = '" & rstF!
FeeType & "';"
DoCmd.RunSQL strUpdate
strUpdate = "UPDATE tbl_Recipts SET
tbl_Recipts.RAmount = " & curAmount _
& " WHERE tbl_Recipts.TMStamp = #" & rstC!
RTM & "# AND RecID = " _
& rstC!RecID & " and RType = 'CR';"
DoCmd.RunSQL strUpdate
Else
strUpdate = "UPDATE tbl_Recipts SET
tbl_Recipts.RType = '" & rstF!FeeType & "',
tbl_Recipts.Account = " & rstF!ContraAcct & "" _
& " WHERE tbl_Recipts.TMStamp = #" &
rstC!RTM & "# AND RecID = " _
& rstC!RecID & " and RType = 'CR';"
DoCmd.RunSQL strUpdate
curBal = rstF!Balance - curAmount
strUpdate = "UPDATE tbl_Charges SET
tbl_Charges.Balance = " & curBal & " WHERE
tbl_Charges.TMStamp = #" & rstF!TMStamp & "# AND RecID
= " _
& rstF!RecID & " and FeeType = '" & rstF!
FeeType & "';"
DoCmd.RunSQL strUpdate
curAmount = 0
End If
rstF.MoveNext
Loop
rstF.Close
Set rstF = Nothing
ReadNext:
rstC.MoveNext
Loop
