Results 1 to 6 of 6
  1. #1
    Mike M is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    3

    Filtering a Combo Box based on a value from another Combo Box(in Multiple Items form)

    Hi,

    Let's assume we have 3 tables:

    Order_Category (Order_Category_ID, Order_Type_Name) with 2 records:
    1, Minor
    2, Major

    Order_Type (Order_Category_ID, Order_Type) with 4 records:
    1, Book
    1, Pencil
    2, Car
    2, House

    Orders (Order_Category_ID, Order_Type, value) with 2 records:
    1, NULL, NULL
    1, NULL, NULL
    2, NULL, NULL

    I want to create a Multiple Items form presenting Orders table with two Combo Boxes:
    1. A combo box to select Order_Category_ID.
    2. A combo box to select Order_Type. When 1 (Minor) is chosen in the first combo box it should show Book and Pencil, when 2 (Major) is chosen it should show Car and House.

    Examples in the Internet show how to do it on a 'single row' forms using the RowSource property.
    I tried to use a query like:

    SELECT Order_Type
    FROM Order_Type
    INNER JOIN Orders ON Order_Type.Order_Category_ID = Orders.Order_Category_ID
    WHERE Order_Category_ID = [comboBoxOrderCategoryID]

    but it sets same values for all records in the Multiple Items form and it should return different values in each rows based on value in the first combo box (Order_Category_ID).

    Thank you for your help! :)



    Best Regards,
    Mike

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You want cascading (dependent) comboboxes.

    Review tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 on comboboxes in Access Forms: Control Basics section.

    Dependent comboboxes don't work very nicely in Continuous or Datasheet forms.
    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
    Mike M is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    3
    Thank you for you answer but this does not resolve my issue.
    As I wrote I have a multiple items form and combo box in each row should depend on another combo box's value in the same row.
    Using dependent comboboxes all of them have the same list of values.

    Are there any other solutions?

    Best Regards,
    Mike

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If the row values aren't changing based on the first combobox then the code is not set up right. Is a matter of getting it in the correct event. Try the combobox Requery in the GotFocus event of the second combobox.
    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
    Mike M is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    3
    Just to confirm we are on the same page. This is the situation that I want to achieve (based on an example I presented in the first post).

    Click image for larger version. 

Name:	example.PNG 
Views:	42 
Size:	5.7 KB 
ID:	6716

    Triggering is not the issue. The issue is that second combo box has to have different values based on first combo box selection (per each row).

    Best Regards,
    Mike

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Yes, that is what I am talking about. Bear in mind that the combobox is one control. Even though it displays repeatedly on the form, it is the same control. So when you modify the RowSource property, that is what will show in all instances of the control until it is requeried. Requery the combobox when it gets focus so the RowSource will be correct for that record.

    I also pointed out that this technique doesn't always work well in Continuous and Datasheet view. If the second combobox is multi-column and set to display the lookup alias, the combobox value will appear blank on the rows that don't have the same value in the first combobox as the current record.

    If you want to provide project I will analyse.
    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.

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

Similar Threads

  1. list form filtering from 1 combo box
    By cooper in forum Forms
    Replies: 5
    Last Post: 08-18-2011, 05:32 PM
  2. Replies: 1
    Last Post: 08-09-2011, 11:13 AM
  3. Filter items shown in Combo Box in sub-form
    By mikeha_99 in forum Access
    Replies: 2
    Last Post: 06-14-2011, 12:54 PM
  4. Replies: 5
    Last Post: 01-02-2011, 10:09 AM
  5. Replies: 0
    Last Post: 08-17-2008, 12:19 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