vba 过滤带有多个复选框的表单
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7013368/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Filtering a form with multiple check boxes
提问by Adamcantvba
What I have is a form which shows company information and also multiple contact subform that shows contact information, seperated by what the contacts job responsibility is(tabbed).
我拥有的是一个显示公司信息的表格，以及显示联系信息的多个联系人子表格，由联系人的工作职责（标签）分开。
I have a combo box on the company form which displays the job responsibilities, and once a responsibility is selected it will show only company records with a contact of the selected responsibility. This is the code;
我在公司表单上有一个组合框，显示工作职责，一旦选择了职责，它将只显示与所选职责的联系人有关的公司记录。这是代码；
Sub SetFilter()
Dim ASQL As String
If IsNull(Me.cboshowcat) Then
' If the combo and all check boxes are Null, use the whole table as the RecordSource.
Me.RecordSource = "SELECT company.* FROM company"
Else
ASQL = "SELECT company.* FROM company INNER JOIN Contacts ON company.company_id = Contacts.company_id WHERE Contacts.responsibility= '" & cboshowcat & "'"
Form_Startup.RecordSource = ASQL
End If
End Sub
The company table will then only show records with a contact of the type specified in the cboshowcat combo box, without showing duplicate companies on the main form.
然后，公司表将仅显示具有 cboshowcat 组合框中指定类型的联系人的记录，而不会在主表单上显示重复的公司。
I then want to apply some further filters based on check boxes on the main form, which relate to fields on the contacts subform. These are activated by a button along with the recordsource code from above;
然后，我想根据主表单上的复选框应用一些进一步的过滤器，这些复选框与联系人子表单上的字段相关。这些是由一个按钮和上面的记录源代码激活的；
Private Sub Command201_Click()
If Nz(Me.cboshowcat) = "" And Me.Check194 = True Or Nz(Me.cboshowcat) = "" And Me.Check199 = True Or Nz(Me.cboshowcat) = "" And Me.Check205 = True Then
MsgBox "Please Select a Job Responsibility"
Cancel = True
Else
SetFilter
If Me.Check194 = True Then
Me.Filter = "cedit <=Date()-90"
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
If Me.Check199 = True Then
Me.Filter = "((copt)='No')"
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
If Me.Check205 = True Then
Me.Filter = "exsite is null"
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
End If
End If
End If
End Sub
At the moment the button filters out the selected contacts by category but if more than 1 of the checkboxes are checked it only uses one of the filters. How do I make it use multiple filters combined depending which tick box is ticked?
目前，该按钮按类别筛选出选定的联系人，但如果选中了 1 个以上的复选框，则它仅使用其中一个筛选器。如何根据勾选的复选框使用多个过滤器组合？
采纳答案by Tim Williams
If you want to filter on multiple criteria then you need to construct the Filter string value to reflect that. Eg:
如果您想根据多个条件进行过滤，那么您需要构建过滤字符串值来反映这一点。例如：
Me.Filter = "cedit <=Date()-90 AND exsite is null"
Start with an empty string and go through the checkboxes, building the string as appropriate: if at the end of that process there's a filter string, apply it.
从一个空字符串开始，通过复选框，根据需要构建字符串：如果在该过程结束时有一个过滤器字符串，则应用它。
Here's a suggestion on how to do this efficiently:
以下是有关如何有效执行此操作的建议：
Dim sFilter as String
sFilter=""
'...
If Me.Check194 Then BuildFilter sFilter, "cedit <=Date()-90"
If Me.Check205 Then BuildFilter sFilter, "exsite is null"
If Me.Check199 Then BuildFilter sFilter, "((copt)='No')"
'... more checkboxes...
Me.FilterOn = Len(sFilter)>0
Me.Filter = sFilter
'....
Sub BuildFilter(byRef sFilt as string, sAdd as string)
If len(sFilt)>0 Then sFilt = sFilt & " AND "
sFilt = sFilt & sAdd
End sub
