Loss of Form's Record Source

[Chaplain Doug]

During the running of some code, a form's recordsource is
changed to null (or empty or blank). Coming into the code
the record source is:
SELECT * FROM [Students Query] WHERE Selected;

It remains set to this until I do a close on a recordset
created in the code. Why would the close reset the
recordsource of the subform? Here is the code running in
the main form (I have commented out some lines while I am
debugging):

Private Sub ArchiveRecords_Click()
On Error GoTo Err_ArchiveRecords_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("Students Archive")
Set rst = Forms![Utilities]![Selected
Students].Form.Recordset
If rst.RecordCount > 0 Then
strMSG = "Do you wish to archive these " + Str
(rst.RecordCount) + " records?"
Response = MsgBox(strMSG, vbYesNoCancel, "Archive
all Selected Records?")
If Response = vbYes Then 'Archive all Selected
records
Application.Echo False
'First move the student records to archives
strSQL = "INSERT INTO [Students Archive] SELECT *
FROM [Students] WHERE Selected=True;"
dbs.Execute strSQL 'This does not prompt
'Then archive the grade records.
With rst
.MoveFirst
While Not .EOF
'First move the records to archives
strSQL = "INSERT INTO [Grades Archive] SELECT
* FROM [Grades] WHERE [StudentID]=" + Str(![StudentID])
dbs.Execute strSQL 'This does not prompt
'Now delete the records from the active table
'strSQL = "DELETE * FROM [Grades] WHERE
[StudentID]=" + Str(![StudentID])
'dbs.Execute strSQL 'This does not prompt
.MoveNext
Wend
rst.Close
End With
'Now delete the student records from the active
table
'strSQL = "DELETE * FROM [Students] WHERE
Selected=True;"
'dbs.Execute strSQL 'This does not prompt
MsgBox "Records moved from active tables to
archives.", vbInformation, "Archival Complete."
End If
Application.Echo True
Forms![Utilities]![Selected Students].Form.Requery
Forms![Utilities]![Select Students].Form.Requery
Else
MsgBox "There are no records Selected.",
vbInformation, "No Records Selected."
End If

Exit_ArchiveRecords_Click:
Exit Sub

Err_ArchiveRecords_Click:
MsgBox Err.Description
Resume Exit_ArchiveRecords_Click
End Sub

 

[Chaplain Doug]

I think I see why the close resets the recordsource. It
is because I set rst to the recordset of the form:

Set rst = Forms![Utilities]![Selected
Students].Form.Recordset

So does this mean that rst is simply set to point to the
existing recordset (rather than creating a new one) and
then when I do the close on rst, I am really closing the
recordset to which it points?

 

[Dirk Goldgar]

I think I see why the close resets the recordsource. It
is because I set rst to the recordset of the form:

Set rst = Forms![Utilities]![Selected
Students].Form.Recordset

Yep, that'd do it, all right.

So does this mean that rst is simply set to point to the
existing recordset (rather than creating a new one) and
then when I do the close on rst, I am really closing the
recordset to which it points?

That is absolutely correct.

 

[Chaplain Doug]

Thanks Dirk.