How do you fill a Recordsource with values from an

Question

I am trying to have a Recordset that holds data copied from another Recordset plus two additional columns. I have mentioned this before in another question. Now there's a problem somewhere in the following code which I can't identify.

If you read the post I've linked, you have already seen this code. I've only changed the part where I append fields to the Recordset as I think there's an error there somewhere. It now looks exactly like my code except the names have been changed.

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
'
    .Fields.Append "val0", adInteger
    .Fields.Append "val1", adInteger
    .Fields.Append "val2", adVarChar, 80
    .Fields.Append "val3", adVarChar, 80
    .Fields.Append "val4", adVarChar, 30
    .Fields.Append "val5", adVarChar, 30
    .Fields.Append "val6", adVarChar, 10
    .Fields.Append "val7", adVarChar, 10
    .Fields.Append "val8", adVarChar, 50, adFldIsNullable
    .Fields.Append "val9", adDBTimeStamp
    .Fields.Append "val10", adVarChar, 50
    .Fields.Append "val11", adSmallInt
    .Fields.Append "val12", adVarChar, 100
    .Fields.Append "val13", adVarChar, 255, adFldIsNullable
    .Fields.Append "val14", adVarChar, 255, adFldIsNullable
    .Fields.Append "val15", adInteger
    .Fields.Append "val16", adInteger
    .Fields.Append "val17", adSmallInt
    .Fields.Append "val18", adSmallInt

    'new Fields for temporary purposes
    .Fields.Append "val19", adBoolean
    .Fields.Append "val20", adVarChar, 50

    .CursorLocation = adUseClient
    .Open , , adOpenDynamic, adLockOptimistic, 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("val1") = RS("some_value")
    cpRS.Fields("val2") = RS("some_value2")
    '...
    'fill the two columns that don't come from the query with default values
    cpRS.Fields("val19") = False
    cpRS.Fields("val20") = ""
    cpRS.Update
    RS.MoveNext
Loop

Set Me.Recordset = cpRS

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

Now there's some strange behaviour (to me at least). When I debug I can see the ADO Recordset being filled with the data from the DAO Recordset. There's no error and all the values are correct. When the Form opens, however, only some cells are filled. The rest of the cells contains "#Name?" which makes me think that a) the datatype I set for the fields is wrong or b) the form doesn't know which values to put into the fields as there are multiple sources that try to fill it (I think I've read about this #Name? error being associated with that kind of error some time ago, but am not sure about it and I wouldn't know where the double assignment occurs).

The structure is roughly the following: Form contains subform. Subform holds several textboxes that have the name of the values I add to the ADO Recordset as Control Source.(This is how my predecessor handled filling forms, i don't know if that is the common way to do this task.) The Subform's Recordsource is not set because I set the ADO Recordset as Recordsource in the Form_Load event (which is where the code is from).

I hope this is enough information to locate the problem, otherwise let me know and I will try to provide more info.

EDIT: I think I should add that there is exactly one column that makes it to the subform which is of the type adVarChar (val4 from the code)

Answer 1

.ControlSource is a control-property (like a textbox) not a recordset field-property.

It binds the recordset-field to the form/report-control.

If you name the adodb recordset-fields same as the dao fields on append. no changes needed in Controlsource:

.Fields.Append "NameOfDaoField0", adInteger

or use

Me.Controls("MyTextbox").ControlSource = "valX"

where valX is the appended new name of the field (val0, val1, ... in your case).

Shouldn't matter where in code.