+ Reply to Thread
Results 1 to 6 of 6

Limit dropdown list based on selection from another list

  1. #1
    Registered User
    Join Date
    04-04-2014
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2010
    Posts
    26

    Limit dropdown list based on selection from another list

    Let me preface my question with the fact that my Access database knowledge is very limited. The database I'm using is an exceedingly simple set-up where I enter the Date, Hours, Type, SubCat, and a Memo into a table to record my daily time. It's all in one table and I've used combo boxes to create drop down lists for the Type and SubCat. See attached image to get a visual on what I'm referring to.

    A new year brings new categories and a need to change this from a two-level structure to a three-level structure. Previously using just "Type" and "SubCat", I now need to add a level between the two labeled "Category" (which I have already done). The functionaility that I didn't previously have (or need) is the ability to limit the list selection based on what is previously selected. For example, if under "Category" I were to select PIT, then I would only want Help Desk, Solutions, DNN & TTCs to be my dropdown options under SubCategory. I would like to customize each Category with it's own subcategory listing. (Please note that it is possible that a Subcategory may be an option under more than one Category.) The attached Excel document is my "map" as to what options should be available once a prior selection is made. I hope that this is making sense!

    Thanks for any help that can be provided. My hope is that I can just change a few settings or make some minor alterations to my existing database to gain this functionality.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,825

    Re: Limit dropdown list based on selection from another list

    Firstly you'll need to add a Category table to your database, it should only need three fields in (based on your example) Type,Category, SubCat (Im not certain but Type may be a reserved word in Access) personally I usually prefix with the field Type so strType, strCategory, strSubCat etc

    Then complete this table with your entries (remember all 3 fields should be completed) so record 1 would be
    NTO
    R&R
    Specialities

    Record2
    NTO
    R&R
    Policies & Procedures

    Record3
    NTO
    PIT
    Helpdesk

    etc etc

    Then set the Record Source for the drop down on your Access form to
    SELECT DISTINCT strCategory FROM tblCategories WHERE (((tblCategory.strType)=[forms]![your Form Name]![comboType]);

    and in the Type combobox put a _Change event of

    Please Login or Register  to view this content.
    then do the same again for the Subcategory combobox
    If someone has helped you then please add to their Reputation

  3. #3
    Registered User
    Join Date
    04-04-2014
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Limit dropdown list based on selection from another list

    Wow, thanks for the quick response! I got as far as creating the new Categories table and adding all the possible combinations as separate records. You've lost me at the next step though because you're referring to a form I think. I don't have any forms. Just the table that you saw a screen shot of. I attached a copy of the database (figured out I could do that if I zipped it) so that you can see what I do/don't have going on. I'm certain you're heading me down the right path and I'm probably just overlooking what I need to do next.....
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,825

    Re: Limit dropdown list based on selection from another list

    ahhh you see you've lost me now, Im old school and dont understand the use of Access if its then going to be used like Excel? Access is designed with a view to keeping the tables out of the way of the user and everything data entry wise, viewing etc via Queries and Forms. Working directly in the table (in my view) causes problems.

    All you are wanting, can be done better and easier for the user in Excel and then pivot tables for the queries.

  5. #5
    Registered User
    Join Date
    04-04-2014
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Limit dropdown list based on selection from another list

    Thanks. I'll eventually figure it out. We can't get Excel to do what we want either and have been using this simple database for a couple of years now very successfully. It may be that I have to resort to creating a form for time entries, but that was something we were hoping to avoid. Thanks anyway!

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    18,989

    Re: Limit dropdown list based on selection from another list

    As PJ indicated, having users work in tables is contrary to good data base design. It allows for to much opportunity for unexpected changes. Additionally, have data validation through combo boxes in a table is a big no-no. Look at this link why you should only have your combo boxes in your forms.

    http://access.mvps.org/access/lookupfields.htm

    Alan

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Populating information based on selection from dropdown list
    By kearbear in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-10-2013, 11:16 PM
  2. Replies: 6
    Last Post: 05-31-2013, 11:17 AM
  3. [SOLVED] Calculation Based On Dropdown list Selection Excel
    By rnohoy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-26-2013, 06:16 AM
  4. Cull dropdown list options based on selection in another dropdown
    By Kiffar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2012, 01:53 AM
  5. Dynamic dropdown list selection based on combo box
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-10-2012, 10:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1