Extracting records based only on check boxes ticked

[Fspinelli]

I have a table with various information (company name, address, etc.) plus five check boxes. One or more of those check boxes may be ticked as it relays a type of status to the end user.

I have a query based on the table's five check boxes plus the Company Name and Record ID #. In the Criteria area I have the word True (no quotes) under each of the check boxes on seperate rows.

I have an unbound form (frmCheckboxes) with the 5 check boxes (same names). Within this form is a subform (sfCheckboxes_R esults). This subform is "supposed" to display all records with only the check boxes ticked on the form...

...However, I am getting all records with any check boxes ticked.

I have a terrible feeling there should be some And/If/or and Elses in the code some where. I just don't know how to write it.

Basically:
If CB1 and CB3 are checked then only display records in the subform who's CBs 1 and 3 are ticked. Else don't display anything (in the subform).

I'm lost on quering check boxes.

Please help.

Thank you and wishing everyone a VERY Happy, Healthy, and Prosperous New Year!

[Mr Key]

I dont see the relationships of your checkBoxes and records in your post!
May you post a sample of your database or explain in details about your problem.

[ADezii]

1. This is a Multi-Step process, but first a couple of Assumptions:
   1. All Check Boxes on the Form, namely CB1...CB5 are 'Unbound', and there are no other Check Boxes aside from these.
   2. The Check Box Names, CB1...CB5, are exactly the same as the Field Names in the underlying Table.
2. A For Each...Next Loop checks every Control on the Form and:
   1. Sees if it is a Check Box.
   2. If it is a Check Box, then sees if it is selected (Checked).
   3. If it is a Check Box, and it is Checked, dynamically builds a partial SQL String with those selected Check Boxes in it.
3. The Code next checks to see if there are any Records in the Table that match the specified Criteria, namely any Check Box(es) checked.
4. If there are any Matching Records the Record Source of the Sub-Form is modified based on the newly created SQL Statement.
5. The Code is posted below; simply change the Name of the strTableName variable to reflect your own Table Name.
6. Any questions, feel free to ask.
   

   Dim ctl As Control
   Dim strBuild As String
   Dim strSQL As String
   Dim strTableName As String
   
   'Substitute your Table Name here
   strTableName = "tblEmployees"
   
   strSQL = "SELECT * FROM " & strTableName & " WHERE "
   
   For Each ctl In Me.Controls
     If ctl.ControlType = acCheckBox Then
       If ctl.Value Then       'Is Control Checked?
         strBuild = strBuild & ctl.Name & " = True AND "
       End If
     End If
   Next
   
   If Len(strBuild) > 0 Then
     'Do any Records exist that match the Criteria?
     If DCount("*", strTableName, Left$(strBuild, Len(strBuild) - 5)) > 0 Then
       Forms!frmCheckBoxes!subfEmployees.Form.RecordSource = strSQL & _
             Left$(strBuild, Len(strBuild) - 5) & ";"    'Remove trailing ' AND '
     End If
   End If

[Fspinelli]

i got it! I had to put this code in the query:

IIf([forms]![frmCB]![cb1],[FoF]=True,True)

I had CB1,2,3,4,5 checked but next to them I inserted a colum and entered that code above for each check box (CB). Not checked for viewing.

CB1 also = FoF so when I clicked on CB1 all records that had their FoF (cb1) ticked would show. Same with if I clicked on FoF (cb1) and NHF (cb2) - only records with those CBs ticked would come up.

So thank you one and all! Great learning experience!