如何使用Access VBA向表中插入多值字段内容

Access的多值查找字段本质依赖隐藏关联表存储数据，普通INSERT语句无法直接批量插入多选值，以下是两种可行解决方案：

方法一：使用DAO Recordset处理多值字段 #

通过主表记录集和多值字段的子记录集，逐个添加选中选项：

Private Sub btnAdd_Click()
    Dim rsMain As DAO.Recordset
    Dim rsMulti As DAO.Recordset
    Dim varItem As Variant
    
    ' 打开主表可编辑记录集
    Set rsMain = CurrentDb.OpenRecordset("Programs", dbOpenDynaset)
    rsMain.AddNew
    
    ' 设置普通字段值
    rsMain!Program_Name = Me.txtProgramName.Value
    rsMain!Objectives = Me.txtObjectives.Value
    
    ' 打开多值字段的子记录集
    Set rsMulti = rsMain!Targetted_Competency.Value
    
    ' 遍历多选控件所有选中项
    For Each varItem In Me.txtTargettedCompetency.ItemsSelected
        rsMulti.AddNew
        rsMulti!Value = Me.txtTargettedCompetency.ItemData(varItem)
        rsMulti.Update
    Next varItem
    
    ' 保存主记录并释放资源
    rsMain.Update
    rsMulti.Close
    rsMain.Close
    Set rsMulti = Nothing
    Set rsMain = Nothing
    
    ' 刷新子窗体
    Programs_subform.Form.Requery
End Sub

方法二：通过关联表手动插入 #

多值字段对应隐藏关联表（命名规则通常为主表名_字段名，可在Access导航栏勾选「系统对象」查看），先插入主记录获取主键，再逐个插入多选值到关联表：

Private Sub btnAdd_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim newProgramID As Long
    Dim varItem As Variant
    
    Set db = CurrentDb
    
    ' 插入主记录，替换单引号避免SQL语法错误
    db.Execute "INSERT INTO Programs (Program_Name, Objectives) " & _
               "VALUES ('" & Replace(Me.txtProgramName, "'", "''") & "', '" & Replace(Me.txtObjectives, "'", "''") & "')"
    
    ' 获取刚插入记录的主键（假设主键字段为Program_ID）
    Set rs = db.OpenRecordset("SELECT @@IDENTITY AS NewID")
    newProgramID = rs!NewID
    rs.Close
    
    ' 逐个插入多选值到关联表
    For Each varItem In Me.txtTargettedCompetency.ItemsSelected
        db.Execute "INSERT INTO Programs_Targetted_Competency (Program_ID, Value) " & _
                   "VALUES (" & newProgramID & ", '" & Replace(Me.txtTargettedCompetency.ItemData(varItem), "'", "''") & "')"
    Next varItem
    
    ' 释放资源
    Set rs = Nothing
    Set db = Nothing
    
    Programs_subform.Form.Requery
End Sub

注意事项 #

• 使用SQL语句时，必须用Replace函数替换字段值中的单引号，避免SQL语法错误；更安全的方式是采用参数查询
• 确保多选控件（如列表框）的MultiSelect属性设置为Extended或Simple，允许多选操作
• 方法一更贴合Access多值字段的设计逻辑，代码可读性更高

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