vba - Is there a way to write to/edit a otherwise read-only Recordset for the context of the form and work with the data?

Question

In my database there is a form that displays records filtered by an abbreviation you can choose from in a combo box. Now in some cases I will want to open another form with the same records as displayed before, but this time there need to be two extra columns and the records (specifically the two new columns) have to be editable. So far my approach looks like this: I fetch the data from the previous form, put it in a new Recordset and add the two columns. Looks as follows:

Dim cpRS As New ADODB.Recordset, RS As DAO.Recordset, cb As checkBox, addr As String
'Creating copy of previously displayed result table
Set cpRS = New ADODB.Recordset
With cpRS
'all the fields needed. I shortened this because it's too long and has no important information
     .Fields.Append "some_value", adInteger
     .Fields.Append "some_value", adInteger
     .Fields.Append "some_value", adSmallInt, , adFldIsNullable

     'new Fields for temporary purposes
     .Fields.Append "first_new_field_checkbox", adBoolean
     .Fields.Append "second_new_field_textbox", adVarChar, 50

     .CursorLocation = adUseClient
     .Open , , adOpenKeyset, adLockPessimistic, 8
End With

'get result set of previous window by applying filter to the same query used before
Dim argv() As String
Dim argRest As String
Dim qdef As DAO.QueryDef
Dim restrictedQuery As String

'When opening this form I hand over OpenArgs which i restore here
'took the code out but "argv" and "argRest" will be used later    

'this is the query that is used in the previous form. i need an extra where clause though so i had to rewrite it.
restrictedQuery = "some very long SQL statement I feel I don't need to put here because it doesn't contribute to the problem." & _
    "If I'm incorrect, please let me know and I will rewrite it to protect the data in it"

Set qdef = CurrentDb.CreateQueryDef("")
qdef.SQL = restrictedQuery
Set RS = qdef.OpenRecordset
Set RS = CurrentDb.OpenRecordset(restrictedQuery, dbOpenSnapshot)
RS.MoveLast
RS.MoveFirst
If RS.RecordCount = 0 Then
    MsgBox "some error text", vbOKOnly, "error title"
    DoCmd.Close acForm, Me.Name
    Exit Sub
End If

'populate new recordset with data from table in previous form
Do Until RS.EOF
'putting the data from the "old" recordset into the new one, shortened again, you get the idea
    cpRS.AddNew
    cpRS.Fields("some_value") = RS("some_value")
    cpRS.Fields("some_value2") = RS("some_value2")
    cpRS.Fields("first_new_field_checkbox") = False
    cpRS.Fields("second_new_field_textbox") = ""
    cpRS.Update
    RS.MoveNext
Loop

Set Me.Recordset = cpRS

RS.Close
Set RS = Nothing
'cpRS.Close  - I removed this
Set cpRS = Nothing

'error here:
Me.RecordSource = cpRS

The problem with this is that the records in the form will be empty. (not exactly empty. To be precise, it says #Name? in every cell in one row.) I'm not really sure what I'm doing wrong. When debugging, everything seems to work, I can see the recordset being filled with the data from my previous form. So my guess is that I'm just failing correctly assigning the new Recordset as source for the form. Likely the line Set Me.Recordset = cpRS is where the incorrect assignment takes place, but I don't know how to fix it, if that's the (only) problem, that is.

The second part of this question has been moved to another thread for clearer structue.

Answer 1

You mix Values and References (stored in Variables) which causes you to accidently close your Forms-Recordset.

Set cpRS = New ADODB.Recordsetcreates a Recordset-Object Instance and stores the Reference of the Instance in the variablecpRS.

Set Me.RecordSet = cpRScopies the Reference fromcpRSto the Forms-RecordSet, which makes both point to the same Instance of the Recordset-Object. It doesn't create a copy of the Object (likeByRefin Function-Arguments in opposite toByVal)!

NowcpRS.Closecloses the Recordset but this is the same as the Forms-Recordset, what leads to an empty form, because the Forms-Recordset is closed!

Just skipcpRS.close(you can destroy the variable withcpRS = Nothingbecause that just destroys the Reference to the Instance stored incpRS, but the Instance is only destroyed if there is no Reference left that points to it, butMe.Recordsetstill contains the Reference!) to get your Form populated (if rest of code is correct which I haven't tested!).

Example:

Private Sub CloseCopyOfRecordsetReference()

Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset

With CurrentDb
    Set rs1 = .OpenRecordset("SELECT * FROM MSysObjects")
    Set rs2 = rs1 'copy Reference
    Set rs3 = rs1 '2. copy Reference

    Set rs1 = Nothing 'this does not affect rs2,rs3
    Debug.Print "rs3.RecordCount: " & rs3.RecordCount

    rs2.Close ' this closes rs3 too!
    Debug.Print "rs3.RecordCount: " & rs3.RecordCount 'Error 3420 here as Recordset is closed
End With

End Sub