Results 1 to 13 of 13
  1. #1
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46

    Triple Combo-Box Filter

    I am trying to make a triple combo-box where the user will select values from the following:

    Directorate
    Department
    Division

    Once the values are selected the user will click the “run query” button and only those records with the selected values will show.

    For example, in the attached database if the following are selected:
    Directorate: CAN
    Department: 3
    Division: 5

    Only those records (five of them) with those criteria would be shown (isolated) for the user to interface with. There will be thousands of records in this database in time.



    I want to use this action in the Form not a subform/subreport. Is this possible? If so can someone please point me in the right direction? I know that the combo-boxes need to be synchronized, etc… I have found several examples but none of them address my current needs.

    Any help with this would be great. Thanks in advance.
    Attached Files Attached Files

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I looked at your DB. I do not see any way your tables relate to each other. So, I do not understand how you can have your comboboxes determine a dataset. Is there a reason you do not store foreign key values in your tables?

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Don't think the OP is trying to develop cascading combos, just wants a query to open with 3 criteria applied.

    Assuming this is the case, you cannot apply the criteria to queries using the docmd.openquery command, instead create a form based on the query and use docmd.openform.

    Alternatively, modify your query (I can see you have made an attempt)

    for directorate, the criteria would be [Forms]![3 Combo Boxes]![Directorate]

    for dept the criteria would be [Forms]![3 Combo Boxes]![Dept]

    etc

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    busdriver3,

    Can you please tell us in simple English what you are trying to accomplish --not Access or database terms?
    Typically Department, directorate, division represent some sort of organizational hierarchy and there are established relationships to show the hierarchy.
    This sort of thing often uses Cascading combo boxes, but your post indicates it may not apply to your situation.

    So more info about WHAT you're trying to do is needed.

    Good luck.

  5. #5
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    Quote Originally Posted by orange View Post
    busdriver3,

    Can you please tell us in simple English what you are trying to accomplish --not Access or database terms?
    Typically Department, directorate, division represent some sort of organizational hierarchy and there are established relationships to show the hierarchy.
    This sort of thing often uses Cascading combo boxes, but your post indicates it may not apply to your situation.

    So more info about WHAT you're trying to do is needed.

    Good luck.
    Ajax and Orange... First, Thank you for helping me... What I am trying to achieve is this...

    My Switch Board (which I will add later) will have three Combo Boxes: Directorate, Department, Division the user will select the desired values, press the button which will open a Form with those selected values and associated data (i.e. name). The form will open in Form view and will display only those records that apply to the Directorate, Department and Division chosen (i.e. cboDept combo box). The displayed records can be edited, and new records can be added.

    I was able to use one of Ajax suggestion to see those records in a query, but I need to have it open in a Form.

    Any help achieving this would be great, thanks again.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I think I see the point Ajax has made. Considering the OP, perhaps the query object can be dependent on the Comboboxes. The main form could be bound to the query object and the combos would act as filter tools. An afterupdate event could requery the form.

  7. #7
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    I tried this but it doesn't work... It just hangs...

    Code:
    Option Compare Database
    Private Sub cmdFilter_Click()
        DoCmd.OpenForm "32 Combo Boxes", , , "DirectorateID=" & cboDirectorate.Value
    End Sub
    Private Sub cmdFilter_Click()
        DoCmd.OpenForm "32 Combo Boxes", , , "DeptID=" & cboDept.Value
    End Sub
    Private Sub cmdFilter_Click()
        DoCmd.OpenForm "32 Combo Boxes", , , "DivID=" & cboDiv.Value
    End Sub
    Private Sub Command115_Click()
        DoCmd.OpenForm "32 Combo Boxes"
    End Sub

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can't have 3 functions all called the same

  9. #9
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    LOL... Man I am a Soup-Sandwich... Let me try again... Thanks

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you probably want to combine all three criteria into one statement - and you don't need .value - that is the giv en default

    Code:
    Private Sub cmdFilter_Click()
        
        DoCmd.OpenForm "32 Combo Boxes", , , "DirectorateID=" & cboDirectorate & " AND DeptID=" & cboDept &  "AND DivID=" & cboDiv
    
    End Sub

  11. #11
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    I wasn't able to get the code to work (i.e. because I am a hack at this) but I was able to use a Macro to filter the query... Please let me know if you think this will suffice.

    I like the code better because it keeps the file size down, but I can't seem to make that work.

    Please see attached file for review.
    Attached Files Attached Files

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    either way will work - what you have done is what I suggested in post #3.

    The next question- which only you can answer - is whether it works to your users satisfaction. For example, what if they want to see all records for a directorate and not worried about branch and division. But that will be for another thread

  13. #13
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    Ajax... Thanks for sharing your ideas, help and guidance... The users will always need to select all three (i.e. Dir, Dept, Div)... I will make amendments to broaden the choices for the Administrators now that I have a working model.

    Thanks again.

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

Similar Threads

  1. Replies: 11
    Last Post: 09-03-2015, 11:12 AM
  2. Why do double, triple, joins....
    By Access_Novice in forum Access
    Replies: 2
    Last Post: 08-23-2014, 06:04 AM
  3. Triple many to many relationship
    By fabiobarreto10 in forum Database Design
    Replies: 0
    Last Post: 04-30-2012, 02:10 PM
  4. Triple nested queries
    By niculaegeorge in forum Queries
    Replies: 5
    Last Post: 07-25-2011, 07:04 AM
  5. Triple primary key
    By snoopy2003 in forum Database Design
    Replies: 1
    Last Post: 02-23-2011, 09:18 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