Results 1 to 12 of 12
  1. #1
    Hendro623 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2016
    Posts
    14

    Need help filtering multiple fields using check box on form for export

    Hello, i've been searching everything for what im trying to do and havent had any luck so i'm hoping somebody could assist. What i'm looking to do is to have multiple check boxes on a form with an after update VBA code that filters the datasheet in the subform. The goal is to be able to check the fields for the data that i'm looking for, then export the filtered selection.

    Below is my current database setup, any assistance at all is appreciated!!

    Table:
    • tblCarriers


    Fields:
    • DryVan (Yes/No)
    • Reefer (Yes/No)
    • Flatbed (Yes/No)


    Form:
    • frmMainMenu


    Subform:
    • frmCarriers


    Checkboxes:
    • DryvanCBox
    • ReeferCBox
    • FlatbedCBox
    • Select All
    • Clear Selection


  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Several approaches you could take:
    - use a combo box with those choices
    - use a multi select listbox and drop the select all and clear selection controls
    - use listboxA to move selections to listboxB and use B choices

    I'd go with option 2, especially since Select All doesn't allow you to pick only 2 in a combo box. It's not clear to me what part of this you want assistance with - form design, methodology or sql building.
    Last edited by Micron; 10-28-2022 at 11:33 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Hendro623 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2016
    Posts
    14
    Im looking for how to setup the VBA so that it can filter a datasheet based on criteria across multiple fields.

    Ex. Show me the records that have BOTH DryVan and Reefer checked to yes, or BOTH Reefer and DryVan. Im a rookie when it comes to access VBA, does it have to be a combo or list box? I simply have check boxes on the main menu form that trigger the search value of 0 or -1. Maybe i'm going about this the wrong way, i'm just stumped and looking for any suggestions.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    So if reefer and dryvan are ticked, you want all of those types that are yes, and flatbed = no?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    does it have to be a combo or list box?
    No, it doesn't. But your way is inflexible. If you ever need to add/remove/rename a type you'll have to add to/modify the form design. If you use a listbox/combobox based on a value list, same thing but at least you would not have to add controls; you'd add to the list. The best approach to eliminate design edits is to use a control that gets its row values from a table or query. Then you only add a new type record to the table. There are other reasons not to use checkboxes (if they're bound to yes/no fields: http://allenbrowne.com/NoYesNo.html) but they seem more complex than what you're into right now.
    As for code to build and run a select query, there are tons of examples for that out there. Try searching on
    ms access vba build dynamic sql

    Here's one: http://allenbrowne.com/ser-62.html
    Last edited by Micron; 10-28-2022 at 02:02 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    You have to go back to the drawing board. Your table design is incorrect. Only one field type belongs in the carrier table. Your database should at least look like this:
    Click image for larger version. 

Name:	carrier.jpg 
Views:	20 
Size:	10.1 KB 
ID:	49016
    Groeten,

    Peter

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Good catch. I skipped over reading the table/field part of the post, having just the checkbox thing on my old brain. My bad.
    You're not suggesting (Y/N) should be part of the field name I hope.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Quote Originally Posted by Micron View Post
    You're not suggesting (Y/N) should be part of the field name I hope.
    No I don't. I just want to make clear it is a yes/no field. OP can use it to select types to be exported.
    Groeten,

    Peter

  9. #9
    Hendro623 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2016
    Posts
    14
    The main objective of this is to be able to filter by the criteria of equipment type and areas that each carrier may cover for a specific job. Example - carrier 123 may cover midwest area but only runs Reefers and carrier456 covers midwest and runs reefers and DryVans. I tried creating a value list with allowing multiple entries which seemed like it would work, but the VBA wasnt being too friendly. I understand the logic of separating the tables but how would i handle the event where a carrier has all 3 equipment types?

    Maybe i'm overthinking the whole thing. Even if i could simply get the subform to export based on what is physically filtered, that would work as well.

  10. #10
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    I assumed that a carrier runs one equipment type. So my model is not right. But the principle remains the same. Only now you need three tables instead of two: carrier, (equipment?) type and a junction table to create a many-to-many relationship between the two.
    I have no access to Access now. I will provide details later.

    In your original question you did not mention areas. You could build that in a simular way, provided the two are not related. So no rules such as ceaarier1 runs Reefers in the east and DryVans in the west.

    Selecting on both items will complicate things but should be possible.
    Groeten,

    Peter

  11. #11
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    I now use this model (no areas included!):
    Click image for larger version. 

Name:	carriers2.jpg 
Views:	14 
Size:	17.5 KB 
ID:	49043

    To select carriers related to equipmenttypes you want to export, use a query like:
    Code:
    SELECT Carrier FROM tblEquipmentType 
    INNER JOIN (tblCarrier 
    INNER JOIN tblCarrierEquipmentType ON tblCarrier.Carrier_ID = tblCarrierEquipmentType.Carrier_ID) ON tblEquipmentType.EquipmentType_ID = tblCarrierEquipmentType.EquipmentType_ID
    WHERE Export=True;
    Groeten,

    Peter

  12. #12
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    To avoid duplicates, better use:
    Code:
    SELECT DISTINCT Carrier FROM tblEquipmentTypeINNER JOIN (tblCarrier
    INNER JOIN tblCarrierEquipmentType ON tblCarrier.Carrier_ID = tblCarrierEquipmentType.Carrier_ID) ON tblEquipmentType.EquipmentType_ID = tblCarrierEquipmentType.EquipmentType_ID
    WHERE Export=True;
    Groeten,

    Peter

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

Similar Threads

  1. Filtering form data with check boxes
    By Bkper087 in forum Access
    Replies: 1
    Last Post: 07-15-2019, 11:33 PM
  2. Replies: 12
    Last Post: 01-27-2016, 12:23 PM
  3. Filtering using multiple fields
    By poppet in forum Forms
    Replies: 2
    Last Post: 05-05-2015, 03:13 AM
  4. Filtering by multiple fields
    By rhubarb in forum Queries
    Replies: 1
    Last Post: 12-05-2013, 04:34 PM
  5. Replies: 2
    Last Post: 02-20-2012, 08:32 AM

Tags for this Thread

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