从多选列表框标准中过滤的代码 [英] Code for Filtering from Mult-select List Box Criteria

问题描述

Hello Everyone，


我有一个带有数据表子表单的主表单，用于按表单查询。

用户选择两个条件后主窗体并单击主窗体上的

cmdShowResults按钮，子窗体根据这两个条件返回记录

。主表单上使用的标准是在两个列表框中选择的值

。当用户点击第一个列表框

（lstCollege）时，它会在第一个列表框（lstAcadPlan）中返回值

。然后，用户单击cmdShowResults以过滤数据表子表单中的
返回记录。这个工作正常，除了一个

问题。两个列表框都设置为单个选择值 - 我现在需要

使第二个列表框（lstAcadPlan）成为一个多选列表框并传递

值过滤器。我不知道如何在我的代码中包含它，并且

想知道是否有人对我应该做什么有任何想法。这是我的

到目前为止只有在

秒列表框中选择了一个值时才能正常工作的代码：


Private Sub cmdShowResults_Click（）

Me.sfrmSearchResults.Visible = True

Me.lblSubformInstructions.Visible = True

Dim strWhere As String

Dim rst记录集


如果Len（Me.lstCollege&""）> 0然后

strWhere =" [College] =''" &安培; Me.lstCollege& "''"

结束如果


如果Len（Me.lstAcadPlan&""）> 0然后

如果Len（strWhere）> 0然后

strWhere = strWhere& 和[AcadPlan] =''" &安培; Me.lstAcadPlan&

"''"

Else

strWhere =" [AcadPlan] =''" &安培; Me.lstAcadPlan& "''"

结束如果

结束如果

表格（Me.Name）（" sfrmSearchResults"）。Form.Filter = strWhere

表格（Me.Name）（" sfrmSearchResults"）。Form.FilterOn = True

结束子


谢谢，


William

解决方案

在我的网站上（见下面的sig）是一个小样本数据库名为

" CreateQueries2.mdb"。此数据库中的表单6说明了如何使用多选列表框在代码中创建

Where条件。有关详细信息，请参阅表格背后的代码

。总的来说，它的工作原理如下：


For i = 0 to lstAcadPlan.ListCount - 1

if lstAcadPlan.Selected（i）Then

strWhere = strWhere& "" &安培; lstAcadPlan.Column（0，i）& "''，"

结束如果

下一个我

strWhere =左（strWhere，Len（strWhere） - 2）& "）;"


此代码循环遍历列表框中的值。当它达到选择

的那个时，它会添加到Where子句中。当它到达结尾时，它会在Where子句的最后一个逗号中消除

。


（strWhere子句的实际语法将根据细节而有所不同

你的列表框控件。）


-

- 罗杰卡尔森
www.rogersaccesslibrary.com

回复：Roger dot Carlson在Spectrum-Health dot Org

" William Wisnieski" < WW ******** @ admissions.umass.edu>在留言中写道

news：3f ******** @ news-1.oit.umass.edu ...

大家好，

我有一个带有数据表子表单的主表单，我用它来表单查询。
用户在主窗体上选择两个条件并单击主窗体上的
cmdShowResults按钮后，子表单根据这两个标准返回记录。主表单上使用的标准是在两个列表框中选择的值。当用户点击第一个列表框
（lstCollege）时，它会在第一个上面的第二个列表框（lstAcadPlan）中返回值
。然后，用户单击cmdShowResults以过滤并返回数据表子表单中的记录。除了一个问题之外，这个工作正常。两个列表框都设置为单个选择值 - 我现在需要
来使第二个列表框（lstAcadPlan）成为多选列表框并将
值传递给过滤器。我不知道如何在我的代码中包含它，并且
想知道是否有人对我应该做什么有任何想法。这是我到目前为止的代码，只要在第二个列表框中只选择了一个值就可以正常工作：

Private Sub cmdShowResults_Click（）
Me.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere as String
Dim rst作为记录集

如果Len（Me.lstCollege& ""）> 0然后
strWhere =" [College] =''" &安培; Me.lstCollege&如果Len（Me.lstAcadPlan&""）>   0然后
如果Len（strWhere）> 0然后
strWhere = strWhere& 和[AcadPlan] =''" &安培; Me.lstAcadPlan&
"''"
Else
strWhere =" [AcadPlan] =''" &安培; Me.lstAcadPlan& "''"
结束如果
结束如果
表格（Me.Name）（" sfrmSearchResults"）。Form.Filter = strWhere
表格（Me.Name） （" sfrmSearchResults"）。Form.FilterOn = True
结束子

谢谢，

威廉

On Mon，22 Dec 2003 10:41:45 -0500，William Wisnieski

< ww ******** @ admissions.umass.edu>写道：


检查SelectedItems集合。然后在你的WHERE语句中构建一个IN子句作为

的一部分：

....和AcadPlan in（''aaa''，''bbb''，'' ccc''）


-Tom。

大家好，

我有一个主要的我用来按表单查询的数据表子表单。
用户在主窗体上选择两个条件并单击主窗体上的
cmdShowResults按钮后，子窗体返回记录
基于这两个标准。主表单上使用的标准是在两个列表框中选择的值。当用户点击第一个列表框
（lstCollege）时，它会在第一个上面的第二个列表框（lstAcadPlan）中返回值
。然后，用户单击cmdShowResults以过滤并返回数据表子表单中的记录。除了一个问题之外，这个工作正常。两个列表框都设置为单个选择值 - 我现在需要使第二个列表框（lstAcadPlan）成为多选列表框并将
值传递给过滤器。我不知道如何在我的代码中包含它，并且想知道是否有人对我应该做什么有任何想法。这是我到目前为止的代码，只要在第二个列表框中只选择了一个值就可以正常工作：

Private Sub cmdShowResults_Click（）
Me.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere as String
Dim rst作为记录集

如果Len（Me.lstCollege& ""）> 0然后
strWhere =" [College] =''" &安培; Me.lstCollege&如果Len（Me.lstAcadPlan&""）>   0然后
如果Len（strWhere）> 0然后
strWhere = strWhere& 和[AcadPlan] =''" &安培; Me.lstAcadPlan&
"''"
Else
strWhere =" [AcadPlan] =''" &安培; Me.lstAcadPlan& "''"
结束如果
结束如果
表格（Me.Name）（" sfrmSearchResults"）。Form.Filter = strWhere
表格（Me.Name） （" sfrmSearchResults"）。Form.FilterOn = True
结束子

谢谢，

威廉

感谢您的回复Tom。


我不熟悉IN子句。 aaa，bbb，ccc代表什么？第二个列表框的

值将根据在

第一个列表框中选择的内容而改变。例如，在第一个列表框中选择的每个学院最多可以有50个不同的学术计划

。另外，什么是

" SelectedItems"收藏？


William

" Tom van Stiphout" <到***** @ no.spam.cox.net>在消息中写道

news：7n ******************************** @ 4ax.com ...

On Mon，22 Dec 2003 10:41:45 -0500，William Wisnieski
< ww ******** @ admissions.umass。 EDU>写道：

检查SelectedItems集合。然后构建一个IN子句作为你的WHERE语句的一部分：
...和AcadPlan in（''aaa''，''bbb''，''ccc''）
< -Tom。

大家好，

我有一个主表单，其中包含我用来按表单查询的数据表子表单。
用户在主窗体上选择两个条件并单击主窗体上的
cmdShowResults按钮后，子窗体将根据这两个条件返回记录。主表单上使用的标准是在两个列表框中选择的值。当用户点击第一个列表框
（lstCollege）时，它返回第一个列表框（lstAcadPlan）
中的值。然后，用户单击cmdShowResults以过滤并返回数据表子表单中的记录。除了一个问题之外，这个工作正常。两个列表框都设置为单个选择值 - 我现在需要
来制作第二个列表框（lstAcadPlan）一个多选列表框并将
值传递给过滤器。我不知道如何在我的代码中包含它，并且
想知道是否有人对我应该做什么有任何想法。这是代码
到目前为止，只要在第二个列表框中选择了一个值就可以正常工作：

私有子cmdShowResults_Click（）
我.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere as String
Dim rst as Recordset

如果Len（Me.lstCollege&" ;"）> 0然后
strWhere =" [College] =''" &安培; Me.lstCollege&如果Len（Me.lstAcadPlan&""）>   0然后
如果Len（strWhere）> 0然后
strWhere = strWhere& 和[AcadPlan] =''" &安培; Me.lstAcadPlan&
"''"
Else
strWhere =" [AcadPlan] =''" &安培; Me.lstAcadPlan& "''"
结束如果
结束如果
表格（Me.Name）（" sfrmSearchResults"）。Form.Filter = strWhere
表格（Me.Name） （" sfrmSearchResults"）。Form.FilterOn = True
结束子

谢谢，

威廉

Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) based
on the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need to
make the second list box (lstAcadPlan) a multi-select list box and pass the
values to the filter. I have no idea how to include that in my code and was
wondering if anyone had any ideas on what I should do. Here is the code I
have so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_Click()
Me.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = ''" & Me.lstCollege & "''"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = ''" & Me.lstAcadPlan &
"''"
Else
strWhere = "[AcadPlan] = ''" & Me.lstAcadPlan & "''"
End If
End If
Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub

Thank you,

William

解决方案

On my website (see sig below) is a small sample database called
"CreateQueries2.mdb". Form 6 in this database illustrates how to create a
Where condition in code with a multi-select listbox. See the code behind
the form for details. In broad outline, it will work something like this:

For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "''" & lstAcadPlan.Column(0, i) & "'', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

This code loops through the values in the listbox. When it reaches one that
is selected, it adds to the Where clause. When it reaches the end, it lops
the last comma off of the Where clause.

(The actual syntax of the strWhere clause will vary depending on details of
your listbox control.)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...

Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) based
on the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need to make the second list box (lstAcadPlan) a multi-select list box and pass the values to the filter. I have no idea how to include that in my code and was wondering if anyone had any ideas on what I should do. Here is the code I
have so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_Click()
Me.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = ''" & Me.lstCollege & "''"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = ''" & Me.lstAcadPlan &
"''"
Else
strWhere = "[AcadPlan] = ''" & Me.lstAcadPlan & "''"
End If
End If
Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub

Thank you,

William

On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> wrote:

Check the SelectedItems collection. Then build an IN clause as part of
your WHERE statement:
.... and AcadPlan in (''aaa'', ''bbb'', ''ccc'')

-Tom.

Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) based
on the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need to
make the second list box (lstAcadPlan) a multi-select list box and pass the
values to the filter. I have no idea how to include that in my code and was
wondering if anyone had any ideas on what I should do. Here is the code I
have so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_Click()
Me.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = ''" & Me.lstCollege & "''"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = ''" & Me.lstAcadPlan &
"''"
Else
strWhere = "[AcadPlan] = ''" & Me.lstAcadPlan & "''"
End If
End If
Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub

Thank you,

William

Thank you for your reply Tom.

I''m not familiar with the IN clause. What do aaa, bbb, ccc represent? The
values of second list box will change depending on what is selected in the
first list box. For example, there can be up to 50 distinct Academic Plans
for each College chosen in the first list box. Also, what are the
"SelectedItems" collection?

William
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:7n********************************@4ax.com...

On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> wrote:

Check the SelectedItems collection. Then build an IN clause as part of
your WHERE statement:
... and AcadPlan in (''aaa'', ''bbb'', ''ccc'')

-Tom.

Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) basedon the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need tomake the second list box (lstAcadPlan) a multi-select list box and pass thevalues to the filter. I have no idea how to include that in my code and waswondering if anyone had any ideas on what I should do. Here is the code Ihave so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_Click()
Me.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = ''" & Me.lstCollege & "''"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = ''" & Me.lstAcadPlan &
"''"
Else
strWhere = "[AcadPlan] = ''" & Me.lstAcadPlan & "''"
End If
End If
Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub

Thank you,

William

这篇关于从多选列表框标准中过滤的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！