Multiple Combobox Filter with Clear Option

[aas4mis]

I haven't had much luck with specific controls, their properties and loops in the past. I thought I would share this tidbit of code, feel free to modify/modularize in any way to suit your needs. This function may be called from the combobox after_update event or a "clear filter" buttons after_update event. I placed this in a module. Oh, one more thing... I happened to have my FK in column 1 of the combobox, this may need changed. Sorry if I "over commented", I have been lost so many times due to poor comments. If there is a better way to accomplish this please feel free to let me know, from my experience vba for access is limited in this area.

Public Function frmSearch_cbxFilter(Optional clear As Boolean)
 
Dim frm As Form
Dim sbFrm As SubForm
Dim ctrl As Control
Dim cbxItem As Variant
Dim cbxValue As Variant
Dim strFilter As String
Dim count As Integer
Dim i As Integer
 
    Set frm = Forms!frmSearch
    Set sbFrm = frm!sbfrmProdDetailSearch
 
    cbxItem = Array("cbxUser", "cbxDepartment", "cbxComment")   '"Named" Comboboxes
    cbxValue = Array("userID", "departmentID", "commentID")     'Filter Fields | Count must match cbxItem
    count = UBound(cbxItem, 1)                                  'Number of elements in array
 
    strFilter = "1=1"
 
    If clear Then                                               'Clear set to true
        For i = 0 To count
            For Each ctrl In frm.Controls                       'Every control on form
                If ctrl.Name = cbxItem(i) Then                  'Only "Named" controls on form
                    ctrl.Value = ""                             'Clear control value
                End If
            Next
        Next
        sbFrm.Form.FilterOn = False
    Else                                                        'Clear not set
        For i = 0 To count
            For Each ctrl In frm.Controls                       'Every control on form
                If ctrl.Name = cbxItem(i) Then                  'Only "Named" controls on form
                    If Not IsNull(ctrl.Column(1)) Then          'Only update filter if control contains a value
                        strFilter = strFilter & " AND [" & cbxValue(i) & "]=" & ctrl.Column(1) 'Build filter
                    End If
                End If
            Next
        Next
 
        sbFrm.Form.Filter = strFilter
        sbFrm.Form.FilterOn = True
    End If
 
End Function

Good Luck. :)

[aas4mis]

Wow! I posted this over a year ago (I think, seems that long) and no responses... Hope this helped somebody out.

[MMcCarthy]

You got 863 views, so I wouldn't worry too much. Most insights don't get a lot of comments so view count is a better indicator.

[aas4mis]

:)

Thanks Mary. Just trying to give back a little to a site that's helped me so much.

[jimatqsi]

Oh, I like this. I make a lot of forms with multiple filter selections in the heading of the form, and this might simplify things greatly.

I often use check boxes and text boxes, so I'd like to expand on this a bit. Maybe by adding some other arrays to specify the SQL code that goes along with a particular selection of a particular control.

Very interesting. Thanks!

Jim

[aas4mis]

Thanks for the reply Jim. I'm glad this helped you out.