如何用ADO Recordset中的值填充Recordsource? [英] How do you fill a Recordsource with values from an ADO Recordset?

问题描述

我正在尝试创建一个Recordset，该Recordset包含从另一个Recordset加上两个附加列复制的数据.我之前在

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.

如果您阅读了我链接的帖子，那么您已经看到了此代码.我只更改了将字段追加到Recordset的部分，因为我认为某处存在错误.现在看起来与我的代码完全一样，只是名称已更改.

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

现在有一些奇怪的行为(至少对我而言).当我调试时，我可以看到ADO Recordset充满了DAO Recordset中的数据.没有错误，所有值均正确.但是，当表单打开时，仅填充某些单元格.其余单元格包含"#Name?"这使我认为a)我为字段设置的数据类型是错误的，或者b)表单不知道要在字段中输入哪些值，因为有多个试图填充它的源(我想我已经读过这个#Name?错误是在一段时间前与此类错误相关联的，但我不确定，因此我不知道重复分配在哪里发生.

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).

结构大致如下: 表单包含子表单.子窗体包含几个文本框，这些文本框具有我作为控件源添加到ADO Recordset中的值的名称.(这是我的前任处理填充表单的方式，我不知道这是否是执行此任务的常用方法.)未设置Recordsource，因为我在Form_Load事件(代码来自其中)中将ADO Recordset设置为Recordsource.

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.

我想我应该补充一点，就是只有一列使其成为子类型adVarChar(代码中的val4)

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)

推荐答案

.ControlSource是控件属性(如文本框)，而不是记录集字段属性.

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

它将记录集字段绑定到表单/report-control.

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

如果将adodb记录集字段命名为与append上的dao字段相同.无需在Controlsource中进行任何更改:

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

.Fields.Append "NameOfDaoField0", adInteger

或使用

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

其中valX是字段的附加新名称(在您的情况下为val0，val1 ...).

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

在代码中的什么位置都没关系.

Shouldn't matter where in code.

这篇关于如何用ADO Recordset中的值填充Recordsource?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！