Recordset error - No Current Record

[Chuck]

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

' RECORDSET FOR FIRST LOOP

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)

' RECORDESET FOR SECOND LOOP
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")
..........................................................
' THIS IS WHERE I RECEIVE THE ERROR ON THE SECOND TIME
THRU...........................................
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

 

[Allen Browne]

The attempt to MoveFirst will fail if there are no records.

Set rstF = ...
If rstF.RecordCount > 0 Then
curAmount = Nz(rstC!RAmount, 0)
'rest of loop
End If

 

[Guest]

Allen,
You The MAN!
Your right, my test data was out of sync... but the check
for records helps make the app more solid.
Thanks
Chuck