如何在MS Access中用VBA在列表框间移动多列数据？解决运行时错误424

我正在搭建MS Access搜索/过滤界面的后端，目前'search filter'、'search box'、'select filter'和'select component'控件均可正常工作，但在'Component List'（对应控件名SearchFilter_List4）上遇到问题。

需求是通过Add、Add All、Remove和Remove All四个按钮，在'Select Component'（控件名SearchFilter_List3）与'Component List'之间移动整行数据。但点击Add按钮时，仅能将第一列值添加到'Component List'，随后弹出运行时错误424：Object required，错误代码行高亮如下：

Me.SearchFilter_List4.Column(0, Me.SearchFilter_List4.ListCount - 1) = Me.SearchFilter_List3.Column(0, selectedRow)

预期效果是点击Add按钮后，'Component List'先添加与'Select Component'相同的三列标题，再移动整行数据。

以下是当前使用的Add按钮和ListBoxItemExists函数代码：

Add按钮代码 #

Private Sub Add_Button_Click()
    Dim selectedRow As Integer
    selectedRow = Me.SearchFilter_List3.ListIndex
    
    ' Check if a row is selected
    If selectedRow <> -1 Then
        Dim selectedValue As String
        selectedValue = Me.SearchFilter_List3.Column(0, selectedRow)
        
        ' Check if the selected component is already present in SearchFilter_List4
        If ListBoxItemExists(Me.SearchFilter_List4, selectedValue) Then
            MsgBox "Duplication Error: Part already added to component list."
        Else
            ' Check if the table headings are already present in SearchFilter_List4
            If Me.SearchFilter_List4.ColumnCount = 0 Then
                ' Add table headings to SearchFilter_List4
                Me.SearchFilter_List4.ColumnCount = 3
                Me.SearchFilter_List4.ColumnWidths = "1000;1000;1000"
                Me.SearchFilter_List4.AddItem "Part Description"
                Me.SearchFilter_List4.AddItem "EBOM"
                Me.SearchFilter_List4.AddItem "AAR Number"
            End If
            
            ' Add the selected row to SearchFilter_List4 and hide it in SearchFilter_List3
            Me.SearchFilter_List4.AddItem ""
            Me.SearchFilter_List4.Column(0, Me.SearchFilter_List4.ListCount - 1) =       Me.SearchFilter_List3.Column(0, selectedRow) ' Part_Description
            Me.SearchFilter_List4.Column(1, Me.SearchFilter_List4.ListCount - 1) = Me.SearchFilter_List3.Column(1, selectedRow) ' EBOM
            Me.SearchFilter_List4.Column(2, Me.SearchFilter_List4.ListCount - 1) = Me.SearchFilter_List3.Column(2, selectedRow) ' AAR_Number
            Me.SearchFilter_List3.RemoveItem selectedRow
        End If
    End If
End Sub

ListBoxItemExists函数代码 #

Private Function ListBoxItemExists(lstBox As ListBox, value As String) As Boolean
    Dim i As Integer
    
    ' Check if the specified value exists in the given ListBox
    For i = 0 To lstBox.ListCount - 1
        If lstBox.Column(0, i) = value Then
            ListBoxItemExists = True
            Exit Function
        End If
    Next i
    
    ListBoxItemExists = False
End Function

我尝试过使用.List和.Selected属性等多种方法，但均在同一代码行触发错误。

问题原因及修复方案 #

错误核心是列表框表头初始化方式错误：你通过三次AddItem分别添加三个标题，这会在列表框中创建3个单行条目，而非设置3列的表头结构。后续给新行赋值时，列表框结构不符合预期，导致对象引用错误。

1. 正确设置列表框表头

MS Access列表框的表头需通过ColumnHeads属性启用，而非用AddItem添加单行。修改初始化表头的代码段：

' 检查列表框是否已配置列属性
If Me.SearchFilter_List4.ColumnCount = 0 Then
    Me.SearchFilter_List4.ColumnCount = 3
    Me.SearchFilter_List4.ColumnWidths = "1000;1000;1000"
    ' 启用表头
    Me.SearchFilter_List4.ColumnHeads = True
    ' 设置数据源类型为值列表（若未绑定其他数据源）
    Me.SearchFilter_List4.RowSourceType = "Value List"
    Me.SearchFilter_List4.RowSource = "" ' 清空初始值，后续通过AddItem添加数据
End If

若要自定义表头文本，可直接在设计视图中设置列表框的Column Heads属性为“是”，然后在RowSource的第一行填写表头内容（用分号分隔各列），或绑定查询时通过字段别名设置表头。

2. 修复整行添加逻辑

添加新行时，可一次性通过AddItem添加多列数据（用分号分隔），避免逐个赋值列的操作：

' 拼接整行数据
Dim rowData As String
rowData = Me.SearchFilter_List3.Column(0, selectedRow) & ";" & _
          Me.SearchFilter_List3.Column(1, selectedRow) & ";" & _
          Me.SearchFilter_List3.Column(2, selectedRow)
' 添加整行到目标列表框
Me.SearchFilter_List4.AddItem rowData
' 移除源列表框中的选中行
Me.SearchFilter_List3.RemoveItem selectedRow

3. 重复检查逻辑无需修改

由于ColumnHeads = True时，ListCount不包含表头行，原ListBoxItemExists函数的逻辑依然有效，可正常检查数据行是否重复。

完整修复后的Add按钮代码 #

Private Sub Add_Button_Click()
    Dim selectedRow As Integer
    selectedRow = Me.SearchFilter_List3.ListIndex
    
    If selectedRow <> -1 Then
        Dim selectedValue As String
        selectedValue = Me.SearchFilter_List3.Column(0, selectedRow)
        
        If ListBoxItemExists(Me.SearchFilter_List4, selectedValue) Then
            MsgBox "重复错误：该零件已添加到组件列表。"
        Else
            ' 初始化列表框配置（仅首次执行）
            If Me.SearchFilter_List4.ColumnCount = 0 Then
                Me.SearchFilter_List4.ColumnCount = 3
                Me.SearchFilter_List4.ColumnWidths = "1000;1000;1000"
                Me.SearchFilter_List4.ColumnHeads = True
                Me.SearchFilter_List4.RowSourceType = "Value List"
                Me.SearchFilter_List4.RowSource = ""
            End If
            
            ' 一次性添加整行数据
            Dim rowData As String
            rowData = Me.SearchFilter_List3.Column(0, selectedRow) & ";" & _
                      Me.SearchFilter_List3.Column(1, selectedRow) & ";" & _
                      Me.SearchFilter_List3.Column(2, selectedRow)
            Me.SearchFilter_List4.AddItem rowData
            Me.SearchFilter_List3.RemoveItem selectedRow
        End If
    End If
End Sub

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