代码之家 › 专栏 › 技术社区 › John

用于筛选列表框的多个组合框

listbox combobox ms-access vba sql

John · 技术社区 · 7 年前

我有一个包含2个组合框和1个列表框的表单。当我使用按钮和文本框进行搜索时,会填充列表框。每个组合框都独立地过滤列表框,但我不知道如何让它们进入图层。i、 e.如果我在两个组合框中都选择了选项,我希望这两个过滤器都应用于列表框。

我希望执行以下操作之一: a) 使过滤器动态分层或 b) 单击搜索按钮时应用筛选器

我当前的布局是:

组合框:cboJob、CBO公司列表框:lstResume 用于搜索的文本框:txtKeywords 搜索按钮:BTN搜索数据来自qryResume 所有数据均为文本

当前代码:

Private Sub btnSearch_Click()
Dim SQL As String
SQL = "SELECT qryResume.ID, qryResume.Company, qryResume.Job, qryResume.LastUpdated " _
    & "FROM qryResume " _
    & "Where Company LIKE '*" & Me.txtKeywords & "*' " _
    & " OR Job LIKE '*" & Me.txtKeywords & "*' " _
    & "ORDER BY qryResume.Company "
    Me.lstResume.RowSource = SQL
    Me.lstResume.Requery

End Sub

Private Sub cboCompany_AfterUpdate()
Dim SQL As String
SQL = "SELECT qryResume.ID, qryResume.Company, qryResume.Job, qryResume.LastUpdated " _
    & "FROM qryResume " _
    & "WHERE qryResume.Company = '" & cboCompany.Text & "'" _
    & "ORDER BY qryResume.Company"
    Me.lstResume.RowSource = SQL
    Me.lstResume.Requery

End Sub

Private Sub cboJob_AfterUpdate()
Dim SQL As String
SQL = "SELECT qryResume.ID, qryResume.Company, qryResume.Job, qryResume.LastUpdated " _
    & "FROM qryResume " _
    & "WHERE qryResume.Job = '" & cboJob.Text & "'" _
    & "ORDER BY qryResume.Company"
    Me.lstResume.RowSource = SQL
    Me.lstResume.Requery

End Sub

1 回复 | 直到 7 年前

Erik A 7 年前

如果可以使用 .Value 属性或 .Column 集合而不是 .Text :

Private Sub RequerylstResume()
   Dim SQL As String
   SQL = "SELECT qryResume.ID, qryResume.Company, qryResume.Job, qryResume.LastUpdated " _
    & "FROM qryResume " _
    & "WHERE 1=1 "
    If cboJob.Value & "" <> "" Then
        SQL = SQL &  " AND qryResume.Job = '" & cboJob.Value & "'"
    End If
    If cboCompany.Value & "" <> "" Then
        SQL = SQL & " AND qryResume.Company = '" & cboCompany.Value & "'"
    End If
    If Me.TextKeyWords.Value & "" <> "" Then 
        SQL = SQL & " AND (Company LIKE '*" & Me.txtKeywords & "*' " _
        & " OR Job LIKE '*" & Me.txtKeywords.Value & "*') "
    End If
    SQL = SQL & " ORDER BY qryResume.Company" 
    Me.lstResume.RowSource = SQL
    Me.lstResume.Requery
End Sub

然后,无论何时您想要执行搜索,只要调用 RequerylstResume .

您可以这样称呼sub:

Private Sub cboJob_AfterUpdate()
    RequerylstResume
End Sub

并将要调用的sub放在同一模块中,在任何其他sub之外