Baffled with "Record Is Deleted"

[Chaplain Doug]

Access 2002. I have a form that has a three-page tab
control. The first page allows me to select students from
my student table then press a button to move the records
to an archive table. The move works as expected. The
second page allows me to select students from the archive
table to be moved back into the active student table. The
move works except after I make the move I get a series of
messages (dialogues) that baffle me. Moreover, they do
not happen all the time. The message box "Microsoft
Access" comes up numerous times (it seems three times per
person moved) saying "Record is deleted" with an OK
button. This happens immediately on the tab page. It
also happens again when I move to another tab page after
the retrieval from archives. Any thoughts about how to
remedy this? Thanks a lot! The code I use to move the
records back is as follows:

Private Sub RetrieveArchiveRecords_Click()
On Error GoTo Err_RetrieveArchiveRecords_Click
Dim strMSG As String, Response As Variant, rst As
Recordset
Dim strSQL As String, dbs As Database
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(Forms![Archives]![Selected
Students Archive].Form.RecordSource)
If rst.RecordCount > 0 Then
strMSG = "Do you want to move these " + Str
(rst.RecordCount) + " records from archives into the
active database?"
Response = MsgBox(strMSG, vbYesNoCancel, "Retrieve
all Selected Records?")
If Response = vbYes Then 'Archive all Selected
records
Application.Echo False
'First move the student records to the active
table
strSQL = "INSERT INTO [Students] SELECT
StudentID,LastName,FirstName,Middle,DOB,Address,City,State,
" _

& "PostalCode,Inmate,Officer,Staff,Other,Comments FROM
[Students Archive] WHERE Selected=True;"
dbs.Execute strSQL 'This does not prompt
With rst
.MoveFirst
While Not .EOF
'Move the records to active table
strSQL = "INSERT INTO [Grades] SELECT * FROM
[Grades Archive] WHERE [StudentID]=" + Str(![StudentID])
dbs.Execute strSQL 'This does not prompt
'Now delete the records from the archive table
strSQL = "DELETE * FROM [Grades Archive] WHERE
[StudentID]=" + Str(![StudentID])
dbs.Execute strSQL 'This does not prompt
.MoveNext
Wend
.Close
End With
'Now delete the student records from the archive
table
strSQL = "DELETE * FROM [Students Archive] WHERE
Selected=True;"
dbs.Execute strSQL 'This does not prompt
MsgBox "Records moved from archive tables to
active tables.", vbInformation, "Retrieval Complete."
End If
Application.Echo True
Forms![Archives]![Selected Students
Archive].Form.Requery
Forms![Archives]![Select Students
Archive].Form.Requery
Else
MsgBox "There are no records Selected.",
vbInformation, "No Records Selected."
End If

Exit_RetrieveArchiveRecords_Click:
Exit Sub

Err_RetrieveArchiveRecords_Click:
MsgBox Err.Description
Resume Exit_RetrieveArchiveRecords_Click

End Sub

 

[Chaplain Doug]

I also notice that if I exit the form and reopen it, then
I no longer get the "Record is deleted" messages. Still
need help though. Thanks.