CODEWITHSUNDEEP
ms-access
bgmCoder
bgmCoder

Reputation: 6370

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.

For example,

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

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

);

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

Upvotes: 0

Views: 1572

Answers (1)

James Toomey
James Toomey

Reputation: 6092

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

Upvotes: 1

Related Questions