Results 1 to 9 of 9
  1. #1
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86

    Filter Form with Multiple Check Boxes


    I have figured out how to filter using more than one combo box, now I want to the same thing only using check boxes, where the user can pick one or many items by checking the boxes and then click on a button to filter the data. I have created my table two ways, because I was trying to figure out what would work best. First table all the fields I want to filter on are yes/no fields. Then I created the same table with a text field and a value list of either blank or yes, thinking I could use similar code as I did for the combo boxes, but the check boxes in the form header I am trying to filter on must not be text. Can someone help me figure out the code to use and which table design is best for this. I am reading lots of negative things about yes/no fields. Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, I've been through this before with other posters. Filtering on multiple yes/no fields can be very frustrating. I limit my use of yes/no fields. Most of them are for my convenience in tables only I as db administrator view and are used in code and users are not even aware they exist. I have no searches set up for users that involve yes/no fields.

    Would have to know more about your data structure and the output results you are trying to achieve. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    ForTestingNJL.zip
    Attached is a copy of the database I am trying to do a searh form on the form name is frm_PROJ_TYPE_SEARCH

    I have 26 items I want to be able to filter on one or many items. Right now I have check boxes in the header of the form, that I cannot get to work. I would also be open to a list box if it were possbile to select more than one item at a time. No sure if this is possible. I do think one of these options if workable would be cleaner than 26 drop down boxes. I appreciate any input that will help me find a solution to this. Thanks!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Better understanding now. There aren't any Yes/No fields. You want to use checkboxes to identify the values to search for in a single field. The disadvantage is that if you routinely add TypeName values, have to modify form to create more checkboxes. Using 26 comboboxes would actually complicate the issue. A single multi-select listbox would not need to be modified if new TypeName values are added. With either checkboxes or listbox, will need VBA code to dynamically construct the filter criteria.

    Review: http://allenbrowne.com/ser-50.html
    http://www.allenbrowne.com/ser-62.html
    http://allenbrowne.com/ser-62.html
    http://allenbrowne.com/ser-62code.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    The links you posted were of the code I used and modified for my drop down boxes in another search form I did, where there were only 3 choices.
    I have already tried to modify this code without success for this search form. So any help you can provide with the code would be appreciated.
    I think the preferred method now is the check boxes, but I would like to present both and give the pros and cons and get more feedback.
    I found some other sample code for the list, but I am still trying to get it to work.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Allen Browne's code for multi-select listbox should be easily adapted. Would have to see your attempted code. Why didn't it work - error message, wrong results, nothing happens?

    Then there is other example that shows building up the filter string from values in several controls. In your case the code would be like:

    If Me.check1 = True Then
    strWHERE = "fieldname='value1' OR "
    End If

    If Me.check2 = True Then
    strWHERE = strWHERE & "fieldname='value2' OR "
    End If

    ...
    If strWHERE <> "" Then strWHERE = Left(strWHERE, Len(strWHERE)-4)
    Last edited by June7; 01-09-2013 at 02:09 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    Here is what I have now for code. It will filter only one item, even if I have both items checked. I used "OR " and "AND " both in the code and they both only bring back one item when I have both check boxes checked. Thanks for any help.

    Dim strWhere As String 'The criteria string.
    Dim lngLen As Long 'Length of the criteria string to append to.


    'Text field example. Use quotes around the value in the string.

    If Me.ckAQE = True Then
    strWhere = "TypeName='AQE' AND "
    End If

    If Me.CkAirPermit = True Then
    strWhere = "TypeName='AirPermit' AND "
    End If


    lngLen = Len(strWhere) - 4
    If strWhere <> "" Then strWhere = Left(strWhere, Len(strWhere) - 4)
    If lngLen <= 0 Then 'Nah: there was nothing in the string.
    MsgBox "No criteria", vbInformation, "Nothing to do."
    Else 'Yep: there is something there, so remove the " AND " at the end.
    strWhere = Left$(strWhere, lngLen)
    'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
    Debug.Print strWhere

    'Finally, apply the string as the form's Filter.
    Me.Filter = strWhere
    Me.FilterOn = True
    End If

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I forgot to concatenate the strWHERE variable. Corrected my post so look at it again. You must use OR operator.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    Thank you so much, works like a charm!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filter a Form with Multiple Combo Boxes
    By Njliven in forum Forms
    Replies: 6
    Last Post: 01-03-2013, 01:25 PM
  2. Datasheet Filter with Check Boxes
    By EddieN1 in forum Forms
    Replies: 1
    Last Post: 05-17-2012, 06:53 PM
  3. How do I add multiple table entries using check boxes?
    By avarusbrightfyre in forum Access
    Replies: 3
    Last Post: 10-21-2010, 01:09 PM
  4. Filter by Form: Check Boxes.
    By tbh7x in forum Forms
    Replies: 0
    Last Post: 08-18-2010, 09:15 AM
  5. Filter form from multiple combo boxes
    By Bird_FAT in forum Programming
    Replies: 6
    Last Post: 05-19-2010, 09:32 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums