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.
[code=vba]
Public Function frmSearch_cbxFi lter(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!sbfrmProdDe tailSearch

cbxItem = Array("cbxUser" , "cbxDepartment" , "cbxComment ") '"Named" Comboboxes
cbxValue = Array("userID", "department ID", "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.Filt erOn = 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.Col umn(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.Filt er = strFilter
sbFrm.Form.Filt erOn = True
End If

End Function[/code]
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.