根据表单中的多个列表框过滤查询 [英] filter a query based on multiple list boxes in a form

问题描述

我有一个包含两个表的 Access 数据库:联系人"和国家"这些都包含许多字段.

I have an Access database with two tables: "contacts" and "country" These contain a number of fields in each.

我有一个查询过滤器"，它将这两个表放在一起.

I have a query, "Filter", that brings these two tables together.

我想创建一个包含与查询中的字段一样多的列表框的表单.用户可以打开表单并从每个列表框中选择多个数据 - 行源与上面的两个表相关联.然后单击按钮，将显示查询，并根据列表框中的用户选择应用过滤器.如果未选择任何内容，则显示不带过滤器的查询.同样，用户不需要从所有列表框中进行选择.

I want to create a form with as many list boxes as there are fields in the query. A user can open the form and select multiple data from each list box - the row sources are tied back to the two tables above. Then on the click of a button the query would be displayed and filters would be applied dependent on the users selections in the list boxes. If nothing is selected then the query is displayed without filters. Similarly the user is not required to make selections from all the list boxes.

任何帮助将不胜感激.我从其他网站上提取了一些代码，这些代码允许我为单个列表框应用过滤器.困难在于扩展多个列表框.这是一个荒谬的要求吗??

Any help would be much appreciated. I have lifted some code from other websites which has allowed me to apply filters for a single list box. The difficulty is expanding for multiple list boxes. Is this an absurd request??

PS 我可以发布我现有的代码，但我相信这现在是一个红鲱鱼"，最好从头开始.

PS I can post my existing code however I believe that this is now a 'red herring' and would be best starting fresh.

推荐答案

我的理解是你有一个未绑定多选列表框的表单，你想在数据表视图中打开一个查询并基于列表框进行查询选择.

My understanding is you have a form with unbound multi-select list boxes and you want to open a query in Datasheet View and have that query based on the list box selections.

这意味着您必须检查每个列表框的 ItemsSelected 集合并相应地更新查询的 SQL 属性.

That means you must examine the ItemsSelected collection of each list box and update the query's SQL property accordingly.

在我的测试表单上，其中包括一个名为 lstFname 的多选列表框，在列表框中选择名称 Jack、Dave 和 Tim，然后单击命令按钮 (cmdOpenQuery)，创建此 SELECT 语句.

On my test form, which includes a multi-select list box named lstFname, selecting the names Jack, Dave, and Tim in the list box, then clicking the command button (cmdOpenQuery), creates this SELECT statement.

SELECT c.*
FROM Contacts AS c
WHERE c.fname IN ('Dave','Jack','Tim')

然后将该语句保存为名为 qrySearchForm 的查询的 SQL 属性.最后在数据表视图中打开该查询.

Then that statement is saved as the SQL property of a query named qrySearchForm. And finally that query is opened in Datasheet View.

但是，我的示例仅包含一个列表框，而您有多个.因此，您还有更多工作要做来扩展这个简单的示例.

However my example includes only one list box, and you have several. So you have more work ahead to extend this simple example.

这是我表单的代码模块...

Here is my form's code module ...

Option Compare Database
Option Explicit ' <- include this in ALL modules!

Private Sub cmdOpenQuery_Click()
    Const cstrQuery As String = "qrySearchForm"
    Dim strNames As String
    Dim strSelect As String
    Dim varItm As Variant

    strSelect = "SELECT c.*" & vbCrLf & "FROM Contacts AS c"

    For Each varItm In Me.lstFname.ItemsSelected
        strNames = strNames & ",'" & _
            Me.lstFname.ItemData(varItm) & "'"
    Next varItm
    If Len(strNames) > 0 Then
        strNames = Mid(strNames, 2) ' discard leading comma
        strSelect = strSelect & vbCrLf & _
            "WHERE c.fname IN (" & strNames & ")"
    End If

    Debug.Print strSelect
    CurrentDb.QueryDefs(cstrQuery).Sql = strSelect
    DoCmd.OpenQuery cstrQuery
End Sub

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