Subform filtered with Checkboxes

Question

Hi 

I hope someone can help me. I have a form in my database with a subform. I want to filter the subform using checkboxes (many of the the columns I am using in the subform are yes/no responses). The problem is that I do not know how to get the checkboxes to relate to and then filter the data.  Can someone possibly explain this to me? Another question I have is that would it be better to use the check boxes or an option group (Only one of these boxes should be checked at a time). 

Side note, this database is for viewing information only, so nobody can change or add data to it. The checkboxes are basically selecting what type of collateral a company is looking for, each type of collateral has its own column with a yes or no in the cell, depending on if the bank in that row offers that type of collateral. 

This is for a project at work that is due asap, so any help would be greatly appreciated! Thanks!

Answer 1

me.filter="Computer =" & check1.value

me.filter=true

on check1 change event.

That won't work because it would also filter the form to 'Computer = FALSE' if the check box is unchecked by the user, which is not what is wanted.  You'd use the check box's AfterUpdate event procedure and would have to modify the code a little:

With Me.ActiveContol

    If .Value = TRUE Then

        Me.Filter = "Computer = TRUE"

        Me.FilterOn = TRUE

        ' further code here should set the value of

        ' all other check boxes to FALSE

    Else

        ' turn off filter if user unchecks the control

        Me.FilterOn = FALSE

    End If

End With

Answer 2

I'd suggest that you don't filter the subform but restrict its recordset by making its RecordSource property a query which references an option group as a parameter.  The option group can go wherever you like, e.g. in the subform's header or in the parent form immediately above the subform.  If we assume the values of the option group are 1 for Industry, 2 for Aircraft, 3 for Agriculture and so on, the syntax for the query's WHERE clause would be along these lines:

WHERE ((Industry = TRUE

  AND Forms!YourParentForm!YourOptionGroup = 1)

  OR Nz(Forms!YourParentForm!YourOptionGroup,0) <> 1)

AND ((Aircraft = TRUE

  AND Forms!YourParentForm!YourOptionGroup = 2)

  OR Nz(Forms!YourParentForm!YourOptionGroup,0) <> 2)

AND ((Agriculture = TRUE

  AND Forms!YourParentForm!YourOptionGroup = 3)

  OR Nz(Forms!YourParentForm!YourOptionGroup,0) <> 3)

AND etc.............

This assumes the option group is in the parent form.  If it is in the subform it would be referenced with the syntax:

    Forms!YourParentForm!YourSubformControl.Form!YourOptionGroup

Wherever it is located, in its AfterUpdate event procedure Requery the subform control to restrict the subform's recordset.

I hope that you realize that the table design breaks one of the fundamental rules of the database relational model by doing what is known as 'encoding data as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1).  This requires that all data be stored as values at column positions in rows in tables, and in no other way.  In essence what you have is a spreadsheet masquerading as a relational database table.  For this reason this design error is sometimes known colloquially as 'committing spreadsheet'.   It makes querying of the data much more difficult.  With a correctly designed table where the data are all stored as explicit values in multiple rows at a single column position restricting the results on the basis of a value in the one column would  be very simple indeed.

Answer 3

me.filter="Computer =" & check1.value

me.filter=true

on check1 change event.

Answer 4

My boss doesn't want to use those dropdown arrows, wants to use radio buttons along with combo boxes to filter through this database...

Answer 5

This is built-in functionality; no programming needed. See that down arrow at the right end of each column title? Click it, and play around with the filter options.