Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249

    How to restrict drop down selections based on another drop down selection in the same form?

    Hello everyone,



    I'm building my first Access database since 1997 or so and need a little help. In the form I'm creating I have a dropdown selection that is fed by the table Events. Events has an ever growing list of test events that we will be doing. As each new test is added, a new set of test cases will be be added to the table Test Cases that belong to that specific event.

    In the form the employee needs to first select the Event and based on that selection, I would like to have another dropdown where they select the test case they will be preforming. I need this dropdown to limit their selection to one of the Test Cases assigned to that Event. How do I do this?

    Thanks in advance for your time and help,

    Keith

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what are looking for is called 'cascading combos'. If you look to the bottom of this thread in the similar threads section you will see a number of threads on the same subject

  3. #3
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Thanks Ajax, now that I know what they are called I can research it better.

  4. #4
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Okay, for the life of me I can't get this to work. I create a new blank form called Runsheet and added two combo boxes. I named one EventCombo and the other ScenarioCombo. I gave them the following attributes:

    EventCombo
    Row Source: SELECT DISTINCT Scenarios_tbl.Event_Name FROM Scenarios_tlb;
    After Update Code: EventCombo.Requery

    ScenarioCombo
    Row Sorces: SELECT Scenarios_tlbl.Test_Case_Number, Scenarios_tlb.Event_Name FROM Scenarios_tlb WHERE (((Scenarios_tlb.Event_Name)=[Forms]![Runsheet]![EventCombo]));


    From everything I've watched or read, that should work. I should be able to select one of the options in the EventCombo box (this part works) and it should then narrow the selection in ScenarioCombo to the appropriate options (this part does not work).

    What am I missing?

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    In the event after update aren't you wanting to requery the scenario combo box?

  6. #6
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Thanks kd2017, that did it!

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    ScenarioCombo
    Row Sorces: SELECT Scenarios_tlbl.Test_Case_Number, Scenarios_tlb.Event_Name FROM Scenarios_tlb WHERE (((Scenarios_tlb.Event_Name)=[Forms]![Runsheet]![EventCombo]));
    you don't need to reference the form, since it is on the same form

    ScenarioCombo
    Row Sorces: SELECT Scenarios_tlbl.Test_Case_Number, Scenarios_tlb.Event_Name FROM Scenarios_tlb WHERE Event_Name=[EventCombo]

  8. #8
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Thanks Ajax, that is cleaner code.

  9. #9
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Now if I wanted to have the selection made via ScenarioCombo populate some other data from scenarios_tlb on this same Runsheet form, how would I go about that. The new data would probably go into text boxes, as I just want it to be info provided to the employee and not something the can select or change.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Add the fields that you want to show from the scenarios_tbl to the ScenarioCombo rowsource then add textboxes to the form and use the ScenarioCombo.Column(index) as their control source (note that the column collection is 0 based meaning the first column is ScenarioCombo.Column(0).

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

  11. #11
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Quote Originally Posted by Gicu View Post
    Add the fields that you want to show from the scenarios_tbl to the ScenarioCombo rowsource then add textboxes to the form and use the ScenarioCombo.Column(index) as their control source (note that the column collection is 0 based meaning the first column is ScenarioCombo.Column(0).

    Cheers,
    When I do that, the text boxes are filled with
    Code:
    #Name?
    and that can not be edited.

    Does it matter of the data is a number vs. actual text? In other words, the data I want to display from column 3 is a Number and not Short or Long Text.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    No, it does not, you probably forgot to update the ColumnCount property of the combo box. And yes, they will not be editable, I thought that was what you wanted.

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

  13. #13
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Quote Originally Posted by Gicu View Post
    No, it does not, you probably forgot to update the ColumnCount property of the combo box. And yes, they will not be editable, I thought that was what you wanted.

    Cheers,
    I did not update that property. It is 1 now, what should it be and how do I know what to update it to for future knowledge?

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Should be equal to the number of columns you want to display in your textboxes. And you should also update the list of column widths (if you want to hide any of them in the combo use 0 for that column width).

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

  15. #15
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Thanks for the help on this Gicu. I'm trying to learn Access and build this database as part of my work from home stuff, so the help is greatly appreciated.

    I only want one column to show up in each of the text boxes. In TextBox A I want column 3 from Scenarios_tlb and in TextBox B I want column 4 from the same table.

    When I change the Column Count for SenarioCombo to say 3, it does show three columns in that dropdown, but the text boxes still show #Name?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 11-09-2017, 04:11 PM
  2. Cascading Drop Downs Based on Previous Selection
    By KirstyAmanda in forum Forms
    Replies: 6
    Last Post: 05-05-2017, 07:13 AM
  3. Auto text based on drop menu selection
    By mandykoonts in forum Forms
    Replies: 6
    Last Post: 01-06-2014, 04:17 PM
  4. Replies: 8
    Last Post: 12-07-2013, 05:22 PM
  5. Replies: 2
    Last Post: 08-14-2013, 04:29 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