多个组合框可过滤列表框 [英] Multiple Combo Boxes to filter a listbox

问题描述

我有一个带有2个组合框和1个列表框的表单.当我使用按钮和文本框进行搜索时，将填充列表框.每个组合框独立过滤列表框，但我不知道如何将它们分层.也就是说，如果我在两个组合框中都选择了选项，那么我希望这两个过滤器都应用于列表框.

I have a form with 2 combo boxes and 1 listbox. The listbox is populated when I search using a button and a text box. Each combo box independently filters the listbox, but I can not figure out how to get them to layer. i.e. if I have chosen selections in both combo boxes, I would like both of the filters to apply to the listbox.

我想执行以下一项操作: a)使过滤器基于动态层 或者 b)单击搜索按钮时是否应用过滤器

I would like to do one of the following: a) Get the filters to layer dynamically based or b) Have the filters apply when the search button is clicked

我当前的布局是:

组合框:cboJob，cboCompany 列表框:lstResume 搜索文本框:txtKeywords 搜索按钮:btnSearch 数据来自qryResume 所有数据都是文本

Combo Boxes: cboJob, cboCompany ListBox: lstResume Textbox for searching: txtKeywords Search Button: btnSearch data is drawn from qryResume All data is text

当前代码:

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

推荐答案

如果可以使用.Value属性或.Column集合而不是.Text，则可以很容易地将此​​代码重构为以下代码:

You can refactor this code pretty easily to the following, if you could use the .Value property or .Column collection instead of .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.

Then, whenever you want to execute a search, just call RequerylstResume.

您称呼这样的潜艇:

Private Sub cboJob_AfterUpdate()
    RequerylstResume
End Sub

然后将您要调用的子程序放在任何其他子程序之外的同一模块中

And put the sub you want to call in the same module, outside of any other sub

这篇关于多个组合框可过滤列表框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！