vba - MS Access 中的列表框多选

Question

我创建了一个表单来获取所有字段标题名称，但我无法选择多个字段。附上供大家参考。

以下是用于从主表中获取标题的代码：

Private Sub Form_Load()
'Call GetColumnNameFromIndex
'Call List4_Click
Dim rst As New ADODB.Recordset
rst.Open "SELECT * FROM Master_DataBase", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
' Note: adOpenForwardOnly and adLockReadOnly are the default values '
' for the CursorType and LockType arguments, so they are optional here '
' and are shown only for completeness '

Dim ii As Integer
Dim ss As String
For ii = 0 To rst.Fields.Count - 1
    ss = ss & "," & rst.Fields(ii).Name
Next ii

Me.List4.RowSource = ss
Debug.Print ss
Me.Requery

End Sub

标签： vbams-accessms-access-2010

Answer 1

将您的属性设置为简单或扩展。

示例 VBA 代码可能如下所示。

Option Compare Database

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
    
    Set MyDB = CurrentDb()
    
    strSQL = "SELECT * FROM tblCompanies"
    
    'Build the IN string by looping through the listbox
    For i = 0 To lstCounties.ListCount - 1
        If lstCounties.Selected(i) Then
            If lstCounties.Column(0, i) = "All" Then
                flgSelectAll = True
            End If
            strIN = strIN & "'" & lstCounties.Column(0, i) & "',"
        End If
     Next i
     
    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [strCompanyCountries] in (" & Left(strIN, Len(strIN) - 1) & ")"
    
    'If "All" was selected in the listbox, don't add the WHERE condition
    If Not flgSelectAll Then
        strSQL = strSQL & strWhere
    End If
    
    MyDB.QueryDefs.Delete "qryCompanyCounties"
    Set qdef = MyDB.CreateQueryDef("qryCompanyCounties", strSQL)
    
    'Open the query, built using the IN clause to set the criteria
    DoCmd.OpenQuery "qryCompanyCounties", acViewNormal
    
    'Clear listbox selection after running query
    For Each varItem In Me.lstCounties.ItemsSelected
        Me.lstCounties.Selected(varItem) = False
    Next varItem
    
    
Exit_cmdOpenQuery_Click:
    Exit Sub
    
Err_cmdOpenQuery_Click:

   If Err.Number = 5 Then
        MsgBox "You must make a selection(s) from the list", , "Selection Required !"
        Resume Exit_cmdOpenQuery_Click
    Else
    'Write out the error and exit the sub
        MsgBox Err.Description
        Resume Exit_cmdOpenQuery_Click
    End If

End Sub

请根据您的特定需求进行定制。