1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2003 Data Input Forms for Mutually-exclusive and Hierarchically-categorized Re

Discussion in 'Business Applications' started by blothrop, Jan 12, 2011.

Thread Status:
Not open for further replies.
  1. blothrop

    blothrop Thread Starter

    Joined:
    Jan 12, 2011
    Messages:
    6
    I’m working in Access 2003 building a database in which the records will all have the same basic fields, but will be a library of info covering a very wide range of topics. In order to make easier how the information is pulled out by the end user, I want to very strictly control how the info is categorized when it is initially stored in the db. I cannot allow careless input clerks to misspell any of the labels that categorize the record they are entering, or perhaps invent their own labels.
    So, each record starts with several columns of categorization labels. The clerk will be presented with a series of drop-down lists and will select one option from each list in order to categorize the record. Each choice will fill in the corresponding column in the categorization section of the record. The options available in each of these drop-down lists are carefully controlled to represent the only sets of acceptable parameters to describe the information contained in the record at each level of detail. Each successive column in this part of the record, representing one of the drop-down lists, covers the next-deeper level in a hierarch of information labeling for the records. As you follow the columns left to right, you are describing the information in the record with greater and greater detail.

    A simple example: The first drop-down shows the options “animal, vegetable, mineral”. You select “animal”. Upon you making that choice, the system returns to you a list of ONLY ANIMALS that appears in the second drop-down list. You choose “elephant”, and then see that your only options in the third drop-down list are “African, Indian”.

    Your choice in one label list defines which choices are available to you in the next. And, if you change a choice you made higher up in the hierarchy, all choices you made below it get wiped out to be replaced with the new sets of acceptable options.

    I have suceeded in getting this to work just fine with webpage forms receiving active content from a MySql db, using PHP and JavaScripts to refresh the page after each selection of a drop-down list option (though the refreshes made it very slow, depending on web traffic, etc.)

    But, presently, we only want to build the database locally, using Access, and then exporting its content in bulk to an online SQLServer later. (We will then launch a new website that uses ASP.)

    My question is this: for a form controlling this kind of hierarchical decision-making in the input of the information, is it best to use a series of nested subforms (with the content of each getting swapped-out based on the choice made in the drop-down list above it)? Or, is there a simpler way of having conditional queries control how the options are presented in each next drop-down list? Obviously, our database is permeated with countless individual tables containing all of these drop-down list options. (These must be handled dynamically to allow us to add new options to each list in to start new branches in the information hierarchy when expanding the database.)

    Surely, I’m not trying to reinvent a wheel, here. I’m sure this kind of method is used quite commonly. I’m just having trouble figuring out how to make Access 2003 do it. I appreciate any guidance you all can offer. Thank you.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,684
    blothrop, welcome to the Forum.
    It is quite common. The key to successfully doing this is twofold. The first is to base each successive Combo on the selection made in the previous one, the second is to requery each combo once the previous combo has made it's selection.
    If you Advance search this forum for poster OBP and Combo in the text you should find various databases that I have posted that do just that.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/974246

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice