基于表单中的多个列表框筛选查询 [英] filter a query based on multiple list boxes in a form

问题描述

我有一个Access数据库有两个表：联系人和国家
这些包含在每个字段中的数字

我有一个查询，过滤器，这两个表将这两个表。

我想要创建一个窗体与尽可能多的列表框查询中的字段。用户可以打开表单并从每个列表框中选择多个数据 - 行源被绑定回上面的两个表格。然后点击一个按钮，查询将被显示，并且过滤器将被应用，这取决于列表框中的用户选择。如果没有选择任何内容，则查询将显示为不带过滤器。同样，用户不需要从所有列表框中进行选择。

任何帮助都将不胜感激。我已经从其他网站取消了一些代码，允许我为单个列表框应用过滤器。多个列表框的难度正在扩大。这是一个荒谬的要求吗？

PS我可以发布我现有的代码，但我相信这是现在的'红鲱鱼'，并将是最好的开始新鲜。我的理解是你有一个非绑定的多选列表框的形式，你想在数据表视图中打开一个查询，并有基于列表框选择进行查询。这意味着你必须检查每个列表框的 ItemsSelected 集合并更新查询的 SQL 属性相应。

在包含名为 lstFname 的多选列表框的测试表单中，选择然后点击命令按钮（ cmdOpenQuery ），创建这个 SELECT c>语句。
$ b

SELECT c。*
FROM Contacts AS c
WHERE c.fname IN（'Dave'，'Jack'，'Tim'）


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

然而我的例子只包含一个列表框，而且有几个。所以你有更多的工作来扩展这个简单的例子。

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

 选项比较数据库
选项显式'<  - 包含在所有模块中！ 
 $ b $ 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
 
对于每个varItm In Me.lstFname.ItemsSelected 
 strNames = strNames& ，和 _ 
 Me.lstFname.ItemData（varItm）& '
下一页varItm 
如果Len（strNames）> 0然后
 strNames = Mid（strNames，2）'丢弃前导逗号
 strSelect = strSelect& vbCrLf& _ 
WHERE c.fname IN（& strNames&）
 End If 
 
 Debug.Print strSelect 
 CurrentDb.QueryDefs（cstrQuery）。 Sql = strSelect 
 DoCmd.OpenQuery cstrQuery 
 End Sub 
  

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 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.

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

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')

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屋！