选择复选框指示字段值的访问项 [英] Select Access Items where Checkboxes indicate Field Values

问题描述

在我的数据库中，我有一个带有几个是/否列的表.

In my database I have a table with several yes/no columns.

现在，我有一个未绑定的表单，其中包含一些未绑定的复选框和一个列表框，仅用于此绑定到一个特殊的查询.

Now, I have a unbound form with some unbound checkboxes and a listbox bound to a special query just for it.

我想使用表单上的复选框来过滤列表框并在列表中显示具有匹配值的项目.

I want to use the checkboxes on the form to filter the listbox and display items in the list that have matching values.

例如

• 如果我在表单上选中box1，则应显示在列表框中包含field1的任何项目.
• 如果我在表单上选中box2和box3，则包含field2 AND field3的 any 项应显示在列表框中.如果一个项目同时没有field3和field3，则它们的条目不应显示在列表中，因为未选中这些框.

• If I check box1 on the form, any item that contains field1 in the listbox should show.
• If I check box2 and box3 on the form, any item that contains field2 AND field3 should display in the listbox. If an item does not have both field3 AND field3, their entries should not show in the list because the boxes are not checked.

窗体上的每个控件都使用一个单击事件过程来使列表重新查询.

Each control on the form uses an on-click event procedure to cause the list to requery.

现在，这不适用于我.当所有值都与复选框匹配时，我的列表框仅显示项目.因此，如果我选中box1和box2，则 only 从具有 only field1和field2的项目中获取结果.如果项目还具有field3，则不会显示-这是我的问题.

Right now, this doesn't work right for me. My listbox only displays items when ALL the values match the checkbox. So, if I check box1 and box2, I only get the results from items that have only field1 and field2. If the item also has field3, it doesn't display - and this is my problem.

这是到目前为止我的查询:

This is what my query looks like so far:

SELECT MyQuery.title, field1, MyQuery.field2, MyQuery.field3
FROM MyQuery
WHERE (
(
(MyQuery.field1) like [Forms]![Ability Finder]![box1]
)AND(
(MyQuery.field2) like [Forms]![Ability Finder]![box2]
)AND(
(MyQuery.field2) like [Forms]![Ability Finder]![box3]
)

);

也许我会用所有错误的方式来解决这个问题.另外，我有大约20个复选框需要为此执行.

Maybe I'm going about this all the wrong way. Also, I have about 20 checkboxes for which I need to do this.

SELECT MyQuery.cat, MyQuery.nickname, MyQuery.title, MyQuery.level, MyQuery.field1, MyQuery.field2, MyQuery.field3
FROM MyQuery
WHERE (
((MyQuery.field1)=[Forms]![Ability Finder]![box1] Or [Forms]![Ability Finder]![box1] Is Null) 
AND ((MyQuery.field2)=[Forms]![Ability Finder]![box2] Or [Forms]![Ability Finder]![box2] Is Null) 
AND ((MyQuery.field3)=[Forms]![Ability Finder]![box3] Or [Forms]![Ability Finder]![box3] Is Null)
);

推荐答案

不确定是否要在VBA中编写事件处理方面有所作为，但这会使此任务容易得多.您可以在每次单击复选框时动态构造SQL查询，如果您只需通过调整For循环将它们添加到该行下方，则它可以处理任何其他复选框.您为每个复选框的Click事件调用Sql构造函数，然后在构造函数中设置列表框的行源.

Not sure if you want to take a crack at writing the event handling in VBA, but it will make this task a lot easier. You can construct the SQL query dynamically each time a checkbox is clicked, and it can handle any additional checkboxes if you add them down the line simply by adjusting the For loop. You call the Sql constructor function for the Click event of each checkbox, then set the listbox's row source in the constructor function.

Private Sub Check1_Click()

ConstructSqlQuery

End Sub

Private Sub Check2_Click()

ConstructSqlQuery

End Sub

Private Sub Check3_Click()

ConstructSqlQuery

End Sub

...

Private Sub ConstructSqlQuery()

Dim sql As String
Dim numChecked As Integer
Dim checkboxName As String
Dim criteriaBoxName As String

numChecked = 0

'the 1=1 is a dummy value that always returns true. It makes it easier to append additional "and" clauses.
sql = "select field1, field2, field3, ... from MyQuery where 1=1"

For x = 1 To 15
    checkboxName = "Check" & x
    criteriaBoxName = "Text" & x
    If Me.Controls(checkboxName).Value = -1 Then
        sql = sql & " and field" & x & " like '*" & Me.Controls(criteriaBoxName).Value & "*'"
        numChecked = numChecked + 1
    End If
Next

If numChecked = 0 Then
    'if nothing is checked, don't show anything.
    Me.List0.RowSource = ""
Else
    Me.List0.RowSource = sql
End If

Me.List0.Requery

End Sub

这篇关于选择复选框指示字段值的访问项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！