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. :)
