如何将列表框选中项与组合框值传入同一数据表

我有一个包含两个组合框（combo box 1和combo box 2）以及一个列表框的表单。希望将组合框的值与列表框中选中的项存入新行，且每选中一个列表框项就复制一次组合框的值。感谢！

Private Sub Command222_Click()

 ''ADD STUDENTS INTO COHORT TABLE
    On Error GoTo Err:

    Const KEY_VIOLATION = 3022
    Dim ctrl As Control
    Dim strSQL  As String
    Dim strSQL2 As String
    Dim varItem As Variant
    Dim x As Integer


    Set ctrl = Me.lstFirst
     
    If ctrl.ItemsSelected.Count > 0 Then
        For Each varItem In ctrl.ItemsSelected
        
            strSQL = "INSERT INTO tbl_cohorts(studentID,StudentFirstName,StudentLastName) " & _
                "SELECT StudentID,StudentFirstName,StudentLastName, txt_cohortID" & _
                "FROM tbl_students " & _
                "WHERE StudentID = " & ctrl.ItemData(varItem)
                
            CurrentDb.Execute strSQL, dbFailOnError
        Next varItem

        For x = 0 To Me.lstSecond.ListCount - 1
            Me.lstSecond.Selected(x) = True
        Next x
                   
    End If

    strSQL2 = "DELETE * FROM tbl_NamesSelected"
    CurrentDb.Execute strSQL2, dbFailOnError
    Me.lstSecond.Requery

    Exit Sub

Err:
    Select Case Err.Number
        Case KEY_VIOLATION
        Resume Next
        Case Else
        MsgBox Err.Description, vbExclamation, "Error"
    End Select

End Sub

现有代码存在两个核心问题：一是INSERT语句的字段数量不匹配（指定3个字段但SELECT返回4个），二是未引入两个组合框的值。以下是修正后的代码，实现需求功能：

Private Sub Command222_Click()
    ' 将组合框值与选中的列表框项存入tbl_cohorts，每个选中项对应一行
    On Error GoTo ErrHandler

    Const KEY_VIOLATION = 3022
    Dim ctrl As Control
    Dim strSQL As String
    Dim varItem As Variant
    Dim cbo1Val As Variant, cbo2Val As Variant

    ' 获取两个组合框的值，替换为你表单中实际的组合框名称
    cbo1Val = Me.cbo1.Value
    cbo2Val = Me.cbo2.Value

    ' 检查组合框是否已选择值（可选，根据需求调整）
    If IsNull(cbo1Val) Or IsNull(cbo2Val) Then
        MsgBox "请先选择两个组合框的值", vbExclamation
        Exit Sub
    End If

    Set ctrl = Me.lstFirst ' 列表框名称，确保与表单中一致

    If ctrl.ItemsSelected.Count > 0 Then
        For Each varItem In ctrl.ItemsSelected
            ' 构建INSERT语句，包含组合框值和学生数据
            ' 注意：需确保tbl_cohorts表存在对应组合框值的字段，比如Cbo1Value、Cbo2Value，替换为实际字段名
            strSQL = "INSERT INTO tbl_cohorts(studentID, StudentFirstName, StudentLastName, Cbo1Value, Cbo2Value) " & _
                     "SELECT StudentID, StudentFirstName, StudentLastName, '" & cbo1Val & "', '" & cbo2Val & "' " & _
                     "FROM tbl_students " & _
                     "WHERE StudentID = " & ctrl.ItemData(varItem)
            
            ' 如果组合框值是数字类型，去掉单引号，使用下面的语句：
            ' strSQL = "INSERT INTO tbl_cohorts(studentID, StudentFirstName, StudentLastName, Cbo1Value, Cbo2Value) " & _
            '          "SELECT StudentID, StudentFirstName, StudentLastName, " & cbo1Val & ", " & cbo2Val & " " & _
            '          "FROM tbl_students " & _
            '          "WHERE StudentID = " & ctrl.ItemData(varItem)
            
            CurrentDb.Execute strSQL, dbFailOnError
        Next varItem

        ' 保留原有逻辑：选中第二个列表框所有项
        Dim x As Integer
        For x = 0 To Me.lstSecond.ListCount - 1
            Me.lstSecond.Selected(x) = True
        Next x
    End If

    ' 保留原有逻辑：清空临时表并刷新第二个列表框
    CurrentDb.Execute "DELETE * FROM tbl_NamesSelected", dbFailOnError
    Me.lstSecond.Requery

    Exit Sub

ErrHandler:
    Select Case Err.Number
        Case KEY_VIOLATION
            ' 主键冲突时跳过当前记录，继续执行
            Resume Next
        Case Else
            MsgBox "错误：" & Err.Description, vbExclamation
            Resume
    End Select
End Sub

关键注意事项 #

• 字段匹配：确认tbl_cohorts表中存在用于存储两个组合框值的字段，若字段名与示例不同，需替换为实际名称。
• 数据类型适配：组合框值为文本类型时，SQL语句中要用单引号包裹；数字类型则去掉单引号，避免语法错误。
• 空值校验：添加的组合框空值检查可防止插入无效数据，若允许空值可删除该判断。
• 错误处理：保留了主键冲突的跳过逻辑，其他错误会弹出提示信息。

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