ms access filter subform based on multiple checkboxes

[Kobs]

Attached is my sample DB. The checkboxes are in frm_Dependent_Search in Search Tab.

 

[MajP]

The problem is I believe you want to filter your subform not the main form. Change the code to

End With  ' for the me part
With Me.Sub_frm_Search.Form
      .Filter = vFilter
      .FilterOn = True
      Debug.Print vFilter
 End With

However the filter does not make any sense. I assume you want an OR and you do need a Not.
You cannot be Like 01* AND Like 05*.

 

[Kobs]

The subform table is not filtering right. The checkboxes are only filtering one at a time.

 

[MajP]

I simplified the code. I just added the search to the tag property of the check like
'01*'

Private Function FilterSub()
  'MajP
   Dim sFilter As String
   Dim ctrl As Access.Control
   For Each ctrl In Me.Controls
     If ctrl.ControlType = acCheckBox And ctrl.Tag <> "" Then
       If ctrl.Value = True Then
         If sFilter = "" Then
           sFilter = "CRN like " & ctrl.Tag
         Else
           sFilter = sFilter & " OR CRN Like " & ctrl.Tag
        End If
       End If
     End If
    Next ctrl
     With Me.Sub_frm_Search.Form
      .Filter = sFilter
      .FilterOn = True
      Debug.Print sFilter
     End With
End Function

I got rid of the event handlers and made a single function to handle the events.

 

[Kobs]

Great. One more thing. Can you do if all is checked show all records(Select All), if none are checked dont display anything (De-Select All).

 

[MajP]

Private Sub Form_Load()
'add this
FilterSub
End Sub

Private Function FilterSub()
  'MajP
   Dim sFilter As String
   Dim ctrl As Access.Control
   For Each ctrl In Me.Controls
     If ctrl.ControlType = acCheckBox And ctrl.Tag <> "" Then
       If ctrl.Value = True Then
         If sFilter = "" Then
           sFilter = "CRN like " & ctrl.Tag
         Else
           sFilter = sFilter & " OR CRN Like " & ctrl.Tag
        End If
       End If
     End If
    Next ctrl
   'If nothing selected then make a bogus filter
    If sFilter = "" Then sFilter = "TRUE = FALSE"
    With Me.Sub_frm_Search.Form
      .Filter = sFilter
      .FilterOn = True
      Debug.Print sFilter
     End With
End Function

Add the load event, and add a bogus filter when no checks selected.

 

[Kobs]

What will be the code I will put at Select All and De-Select All buttons?

 

[MajP]

Loop your checkboxes again like in the filter.

Private Sub cmdSelectAll_Click()
   Dim ctrl As Access.Control
   For Each ctrl In Me.Controls
     If ctrl.ControlType = acCheckBox And ctrl.Tag <> "" Then
       ctrl = True
     End If
  Next ctrl
  FilterSub
End Sub
Private Sub cmdDeSelectAll_Click()
    Dim ctrl As Access.Control
   For Each ctrl In Me.Controls
     If ctrl.ControlType = acCheckBox And ctrl.Tag <> "" Then
       ctrl = False
     End If
  Next ctrl
  FilterSub

 

[Kobs]

how to create a report (print preview) based on the filtered subform?

 

[MajP]

Modified code

Private Sub Form_Load()
 FilterSub
End Sub

Private Function FilterSub()
  'MajP
   Dim sFilter As String
   sFilter = GetFilter
   With Me.Sub_frm_Search.Form
      .Filter = sFilter
      .FilterOn = True
      Debug.Print sFilter
     End With
End Function
Private Function GetFilter() As String
   Dim sFilter As String
   Dim ctrl As Access.Control
   For Each ctrl In Me.Controls
     If ctrl.ControlType = acCheckBox And ctrl.Tag <> "" Then
       If ctrl.Value = True Then
         If sFilter = "" Then
           sFilter = "CRN like " & ctrl.Tag
         Else
           sFilter = sFilter & " OR CRN Like " & ctrl.Tag
        End If
       End If
     End If
    Next ctrl
    'If nothing selected then make a bogus filter
     If sFilter = "" Then sFilter = "TRUE = FALSE"
    GetFilter = sFilter
End Function
Private Sub cmdPreviewReportcbo_Click()
  DoCmd.OpenReport "rpt_All_municipalities", acViewPreview, , GetFilter()
End Sub

 

[Kobs]

the whole code is for the report?

 

[MajP]

No. For both. Just modified the code to reduce duplication.

 

[Uncle Gizmo]

Re the Code HERE:-

Loop your checkboxes again like in the filter.

If you look at the Nifty Tips page on my website:- https://www.niftyaccess.com/nifty-tips/ there's a tip near the bottom titled "Command Buttons - Convert Two in to One" you could replace the two command buttons "cmdSelectAll" & "cmdDeSelectAll" with one button YouTube:-

Lock/Unlock Controls 4 - Nifty Access

 

[Kobs]

MajP so I'll just replace the code with the modified code except the select and de-select?

 

[Kobs]

MajP so I'll just replace all the code for checkbox with the modified code except the select and de-select?

 

[MajP]

Yes.

 

[Kobs]

MajP can you recommend any reading materials/ebook about access and vba?

 

[MajP]

There are a couple of threads on this site for good resources on vba. I am a book person, others like videos. For software I usually by older used books because you can get a 100 dollar book for 5 bucks. The Access Inside Out books are a good start and very thorough. As long as it is after 2007 version you should be fine. Not much has changed especially on basics. I see a 2013 version for 8.00. If you get through that the Bible is
Litwin and Getz Access Developers Handbook (2002). This goes into advanced concepts. Search here for threads on other recources.

 

[strive4peace]

MajP can you recommend any reading materials/ebook about access and vba?

hi Kobs,

here are a couple links that are free:

Access Basics
http://www.accessmvp.com/strive4peace
Free 100-page book that covers essentials in Access

Learn VBA
http://www.accessmvp.com/strive4peace/VBA.htm

 

[Kobs]

MajP,
I copied all the code from your sample database to my final database but nothing is working? Am i missing something? Please Help.