item not found in recordset error

[krc777]

I have the following code:
Do While rst.EOF <> True
myYear = rst.Fields("PmtYr")
myIPR = rst.Fields("IPR")
myCat = rst.Fields("ASD")
myVal = rst.Fields(TotmonVal)
rst.MoveNext

If rst.EOF <> True Then
myNVal = rst.Fields(TotmonVal)
myNextYr = rst.Fields("PmtYr")
If myVal <> 0 And myNVal <> 0 Then
calcVal = myNVal / myVal - 1
Else
calcVal = 0
End If
If monVal = "Jan" Then
If rst.Fields("PmtYr") <= 2006 Then
rst3.AddNew
rst3.Fields("PmtYr") = myYear
rst3.Fields("IPR") = myIPR
rst3.Fields("ASD") = myCat
rst3.Fields(totval) = calcVal
rst3.Fields("NextPmtYr") = myNextYr
rst3.Update
End If
Else ' If monVal <> "Jan" Then
If rst.Fields("PmtYr") <= 2006 Then
Set rst = db.OpenRecordset("SELECT (" & PrevmonVal & ") from MainRptDataASD Totals where PmtYr = " & myYear & " AND ASD = '" & myCat & "' AND IPR = '" & myIPR & "'")
CurrentDb.Execute ("UPDATE MainRptDataASDTot SET " & totval & "='" & calcVal & "', ASD = '" & myCat & "' where PmtYr = " & myYear & " AND ASD = '" & myCat & "' AND IPR = '" & myIPR & "'")
End If
End If
End If
Loop
Loop
Next

When it moves thru the Currentdb.execute command for the first time and goes back to the Do while rst.eof I get an error Item not found in this collection on the 'myYear = rst.Fields("PmtYr") ' line. That item 'PmtYr' is in the rst referenced db.
Can anyone help?
Thanks.

 

[Bat17]

not sure if I followed it all correctly but it looks like you reset the value of rst. here
Set rst = db.OpenRecordset("SELECT (" & PrevmonVal & ") from MainRptDataASD Totals where PmtYr = " & myYear & " AND ASD = '" & myCat & "' AND IPR = '" & myIPR & "'")

and don't have PmtYr in the recordset you are creating,

HTH

Peter

 

[krc777]

I thought I was moving the pointer to a record so I could insert the next month's data in the same record as January's was recorded. So I don't know what the 'set rst =' line is doing? It is reseting the db?

 

[Bat17]

it's changing rst from your original data set to the new sql.

Petr

 

[krc777]

I opened a third RS so I didn't change the first.
Thanks for you help and setting me in the right direction.
k