Run Time Error 3021 - Access 2002 SP3

[alpinegroove]

I have run into the "No current record" bug in Access 2002 SP3 and have tried to disregard the error in the error handling procedures.

However, when I try to delete the last record in my subform using the Delete button, instead of getting the error, Access now seems to get stuck in a loop of trying to do something, but I am not sure what. The Delete button keeps "flashing" as if someone is continuously clicking it, and the only way to stop this is to force quit the program.

It is very possible that I wrote the code incorrectly. I am pretty new to this and have put the code together from different sources.

Any suggestions?


Thanks

Private Sub DeleteRole_Click()

On Error GoTo Err_DeleteRole_Click

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    
Exit_DeleteRole_Click:

Dim rs As dao.Recordset

Me.Requery
Set rs = Me.RecordsetClone
rs.MoveFirst

Do While rs.EOF = False
If rs![fkRoleID] = 3 Then
    Me.Parent.CourseHasTA2 = "Yes"
Else
    Me.Parent.CourseHasTA2 = "No"
End If
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

Exit Sub

Err_DeleteRole_Click:
    If Err.Number <> 3021 Then
    MsgBox Err.Description
    End If
    Resume Exit_DeleteRole_Click
    
End Sub

[pbaldy]

You have everything in your exit handler. This is the normal structure:

http://www.baldyweb.com/ErrorTrap.htm

[alpinegroove]

Thank you for your reply.
I don't think I understand.
Do you mean that I need to move "Exit_DeleteRole_Click:" or "Exit Sub"?

Since I combined two different codes here, it wouldn't surprise me if the structure is a problem now, though everything worked fine before I added the "If Err.Number <> 3021 Then."

Would you please elaborate a bit more? I am not sure how to resolve this.
Thanks again.

[pbaldy]

Follow the code. You've said after an error:

Resume Exit_DeleteRole_Click

That brings it right back up to the code that causes the error, thus your endless loop. That line should be right above these:

rs.Close
Set rs = Nothing

I wouldn't even rely on error trapping. This is almost certainly the line causing the error:

rs.MoveFirst

and you don't need it.

[alpinegroove]

Thank you very much. I tested both suggestions and they both worked.
I'll go with removing rs.MoveFirst. If I don't need it, there is no reason for it to be there...

[ssanfu]

Thank you very much. I tested both suggestions and they both worked.
I'll go with removing rs.MoveFirst. If I don't need it, there is no reason for it to be there...

There are many ways to write code. With all due respect to Paul, I read some where (some when) that there was no guarantee that when a record set was opened that the current record would be the top of the recordset. So I *always* use

rs.MoveFirst

or

rs.MoveLast
rs.MoveFirst

if I want to fully populate the record set before beginning processing.

This is how I would write (modify) your code:

Private Sub DeleteRole_Click()

   On Error GoTo Err_DeleteRole_Click

   'Select record
   'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70  'depreciated
   DoCmd.RunCommand acCmdSelectRecord

   'Delete record
   'DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70   'depreciated
   DoCmd.RunCommand acCmdDeleteRecord

   Dim rs As DAO.Recordset

   Me.Requery
   Set rs = Me.RecordsetClone
   rs.MoveFirst

'Do While rs.EOF = False
   Do While Not rs.EOF

      'if rs![fkRoleID] can have NULLs, you could use the following line (not both)
      '      If (rs![fkRoleID] = 3) And Not IsNull(rs![fkRoleID]) Then
      If rs![fkRoleID] = 3 Then
         Me.Parent.CourseHasTA2 = "Yes"
      Else
         Me.Parent.CourseHasTA2 = "No"
      End If
      rs.MoveNext
   Loop

Exit_DeleteRole_Click:

   rs.Close
   Set rs = Nothing

   Exit Sub

Err_DeleteRole_Click:
   If Err.Number <> 3021 Then
      MsgBox Err.Description
   End If
   Resume Exit_DeleteRole_Click

End Sub

Just putting in my 2 cents worth....
(BTW ..not tested )

[alpinegroove]

ssanfu, I tested this and it seems to work.
Why is it that I am not getting Error 3021 now?
I thought that earlier it was caused by the rs.MoveFirst.
Is it because the Exit_DeleteRole_Click: is in a different place now?

Thanks

[alpinegroove]

Sorry, of course I am not get the error message. It is being ignored by the "If Err.Number <> 3021 Then."

What is the significance of moving "Exit_DeleteRole_Click:" to a different location?

[ssanfu]

Sorry, of course I am not get the error message. It is being ignored by the "If Err.Number <> 3021 Then."

What is the significance of moving "Exit_DeleteRole_Click:" to a different location?

Did you trace through your original code?

When you hit an error, it would goto "Exit_DeleteRole_Click:",
which would then try and create another recordset,
do requery,
rs.movefirst,
then start another Do Loop,
which would error,
the error handler would return you to "Exit_DeleteRole_Click:",
which would create the record set,
which would....... results in an endless loop.

So, yes, the "Exit_DeleteRole_Click:" line was in the wrong place & had nothing to do with "rs.MoveFirst."

[alpinegroove]

Thank you for the clarification. That makes sense.
I pieced this code together. There are certainly limitations to using code one does not fully understand, but this has been a good learning experience for me, and the code finally works well.
Thanks.