By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,570 Members | 970 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Multiple Combobox Filter with Clear Option

aas4mis
P: 97
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function frmSearch_cbxFilter(Optional clear As Boolean)
  2.  
  3. Dim frm As Form
  4. Dim sbFrm As SubForm
  5. Dim ctrl As Control
  6. Dim cbxItem As Variant
  7. Dim cbxValue As Variant
  8. Dim strFilter As String
  9. Dim count As Integer
  10. Dim i As Integer
  11.  
  12.     Set frm = Forms!frmSearch
  13.     Set sbFrm = frm!sbfrmProdDetailSearch
  14.  
  15.     cbxItem = Array("cbxUser", "cbxDepartment", "cbxComment")   '"Named" Comboboxes
  16.     cbxValue = Array("userID", "departmentID", "commentID")     'Filter Fields | Count must match cbxItem
  17.     count = UBound(cbxItem, 1)                                  'Number of elements in array
  18.  
  19.     strFilter = "1=1"
  20.  
  21.     If clear Then                                               'Clear set to true
  22.         For i = 0 To count
  23.             For Each ctrl In frm.Controls                       'Every control on form
  24.                 If ctrl.Name = cbxItem(i) Then                  'Only "Named" controls on form
  25.                     ctrl.Value = ""                             'Clear control value
  26.                 End If
  27.             Next
  28.         Next
  29.         sbFrm.Form.FilterOn = False
  30.     Else                                                        'Clear not set
  31.         For i = 0 To count
  32.             For Each ctrl In frm.Controls                       'Every control on form
  33.                 If ctrl.Name = cbxItem(i) Then                  'Only "Named" controls on form
  34.                     If Not IsNull(ctrl.Column(1)) Then          'Only update filter if control contains a value
  35.                         strFilter = strFilter & " AND [" & cbxValue(i) & "]=" & ctrl.Column(1) 'Build filter
  36.                     End If
  37.                 End If
  38.             Next
  39.         Next
  40.  
  41.         sbFrm.Form.Filter = strFilter
  42.         sbFrm.Form.FilterOn = True
  43.     End If
  44.  
  45. End Function
Good Luck. :)
Jan 23 '09 #1
Share this Article
Share on Google+
5 Comments


aas4mis
P: 97
Wow! I posted this over a year ago (I think, seems that long) and no responses... Hope this helped somebody out.
Dec 21 '09 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Dec 23 '09 #3

aas4mis
P: 97
:)

Thanks Mary. Just trying to give back a little to a site that's helped me so much.
Dec 23 '09 #4

Expert 100+
P: 1,221
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
Jun 12 '10 #5

aas4mis
P: 97
Thanks for the reply Jim. I'm glad this helped you out.
Jun 14 '10 #6