Results 1 to 6 of 6
  1. #1
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34

    Having trouble layering filters for a subform

    I have a form that shows a query of Machines and Parts. There are many parts to a machine. I am able to filter by machine and part using a combobox by writing out the criteria on the query to read Form!FormName!ComboboxName. This does well for one filter but if I want to do this with two fields, both comboboxes have to be used to get any values back. What I want is to be able to see all of the parts of one machine without having to pick a specific part if I did not want to. How can I accomplish this?

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at this thread, try to implement Ajax's solution for the parts combo.
    https://www.accessforums.net/showthread.php?t=80665
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    Thanks for the help. I am still not understanding the tick method you were talking about in the other post. Is that the best way to filter a query using multiple combo boxes?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    That same logic can be applied to the parts combo, if empty show all parts for the machine. Or you can simply use Like Forms!frmYourForm!cboPartsFilter & "*" in the criteria row for the parts combo (adjust the expression to fit your names).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    Am I supposed to have a field for the parts combo box? And how can you check if the combo box is empty or not?

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    In your original post you said you have a form that you want to filter for a specific machine (via a machine combo) and one specific part if parts combo is populated or all parts for that machine if it is left blank. So depending on how your parts are set up (in a separate tblParts table with PartID (numeric)/PartDescrription (text) fields vs part name (text) ) you could:
    1. use Form!FormName!ComboboxMachine for the criteria in the machine combo and Like Forms!frmYourForm!cboPartsFilter & "*" in the parts combo, making sure the parts combo's bound column is the text part description and not the numeric part ID (as that would return all parts starting with that PartID - i.e. for PartId =1 you would also get PartIds 10,11,12,......,100,101...
    2.Use the same logic from the thread I posted earlier and put Forms!frmYourForm!cboPartsFilter OR Isnull(Forms!frmYourForm!cboPartsFilter)=True in the criteria for the parts combo - probably Acces will split it on two rows like this:
    Click image for larger version. 

Name:	combo.png 
Views:	5 
Size:	54.6 KB 
ID:	41971

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 0
    Last Post: 08-06-2018, 02:28 PM
  2. Replies: 22
    Last Post: 04-21-2017, 08:23 AM
  3. Apply multiple filters to subform
    By Elwood07 in forum Forms
    Replies: 10
    Last Post: 07-06-2015, 12:17 AM
  4. Command Button Layering (ZOrder?)
    By pt5 in forum Programming
    Replies: 6
    Last Post: 08-01-2013, 10:32 AM
  5. Subform with multi filters
    By Aragon.2009 in forum Forms
    Replies: 0
    Last Post: 08-27-2010, 03:01 PM

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