Recordset error - No Current Record

  • Thread starter Thread starter Chuck
  • Start date Start date
C

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
 
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
 
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
 
Back
Top