在Microsoft Access VBA中使用DAO添加记录时，如何设置多值字段？

Access的多值字段无法直接通过分号分隔字符串赋值，也不能直接将另一个Recordset赋值给它，必须通过操作多值字段的子Recordset来逐个添加值。以下是具体解决方法：

错误原因说明 #

• 直接赋值分号字符串：多值字段本质是内嵌子记录集，不支持这种简化赋值方式，必须通过Recordset2接口操作。
• 直接赋值Recordset2：不能将外部Recordset直接绑定到多值字段的子记录集，需要逐条读取目标记录的ID并添加到子记录集中。

方法1：手动添加指定ID值 #

如果已知要添加的Code ID列表（比如1、2、3），可以直接逐个添加：

Dim db As DAO.Database
Dim rsMain As DAO.Recordset
Dim rsMulti As DAO.Recordset2

Set db = CurrentDb
Set rsMain = db.OpenRecordset("Discrepancy", dbOpenDynaset)

rsMain.AddNew
' 先设置主表的其他字段
' rsMain![YourOtherField] = "对应值"

' 获取多值字段的子记录集
Set rsMulti = rsMain![Type].Value

' 逐个添加Code ID
rsMulti.AddNew
rsMulti![Value] = 1
rsMulti.Update

rsMulti.AddNew
rsMulti![Value] = 2
rsMulti.Update

rsMulti.AddNew
rsMulti![Value] = 3
rsMulti.Update

' 关闭子记录集
rsMulti.Close
Set rsMulti = Nothing

' 提交主表记录更新
rsMain.Update

rsMain.Close
db.Close
Set rsMain = Nothing
Set db = Nothing

方法2：从Code表批量读取并添加 #

如果需要从Code表筛选符合条件的记录添加到多值字段，可以遍历Code表的记录集：

Dim db As DAO.Database
Dim rsMain As DAO.Recordset
Dim rsMulti As DAO.Recordset2
Dim rsCode As DAO.Recordset

Set db = CurrentDb
Set rsMain = db.OpenRecordset("Discrepancy", dbOpenDynaset)
' 可添加筛选条件，比如WHERE Code = 'xxx'
Set rsCode = db.OpenRecordset("SELECT ID FROM Code", dbOpenDynaset)

rsMain.AddNew
' 设置主表其他字段
' rsMain![YourOtherField] = "对应值"

Set rsMulti = rsMain![Type].Value

' 遍历Code表记录，逐个添加ID到多值字段
If Not rsCode.EOF Then
    rsCode.MoveFirst
    Do Until rsCode.EOF
        rsMulti.AddNew
        rsMulti![Value] = rsCode!ID
        rsMulti.Update
        rsCode.MoveNext
    Loop
End If

' 清理资源
rsMulti.Close
rsCode.Close
rsMain.Update
rsMain.Close
db.Close

Set rsMulti = Nothing
Set rsCode = Nothing
Set rsMain = Nothing
Set db = Nothing

内容的提问来源于stack exchange，提问作者VCD_WL