Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006

    Unanswered: Help - Control needs to pull data from different places based on criteria

    I've been working on this for so long with no luck. I'm thinking I may need certain VBscripting to get it done. I have a supervisor-employee relationship built on tblMain and tblMain_1 where the SupervisorID links with the ID of the other table. I have a combo box in a subform and I want it to pull first from the SupervisorID field of tblMain and if that is null, pull from a rowsource listing all the IDs from tblMain and then save the selection into the SupervisorID field for the next time the form is opened. I know it probably sounds confusing so I'm attaching a gif image illustrating the best I can on what I want to do. Please help!
    Attached Thumbnails Attached Thumbnails databasehelp.gif  

  2. #2
    Join Date
    Jun 2003

    Drop Down Box and Normalization

    My first comment is that your tables do not appear set up right. Your Main, and Main_1 tables are identical...not good. Do a search about Normalizing Tables

    Without knowing your exact setup, what I think what you need are two table with people names, an associated People_ID and some info; and another table with the relationships between different people....Employee_ID, Manager_ID, and other collumns with info about the relationship if desired.

    In your relationships page, link the People_ID from the first table to both Employee_ID and Manager_ID on the second table.

    Your drop down box will be most easily created using the wizard feature to select where the data is coming from, and where to store it.

    If that is confusing, you can set the drop down box properties...

    Set the control souce to Manager_ID
    Set up a query for the row source to be the People_ID

    Please let me know if this helps.


  3. #3
    Join Date
    May 2006

    Yes, I need to work on normalizing the tables more but those are not two separate tables. One is a duplicate of the other but not an actual physical table. It's kind of confusing but there's a standard when making Supervisor -> Employee relationships on duplicating the table to establish the relationship (only in the relationship's window but not actually duplicating the actual table) In doing this, you avoid having duplicate data (having some of the same IDs in both an "employees" table and a "supervisor/managers" table since employees are also managers and managers are also employees). I do believe what you are suggesting would work if they did come from separate tables but I can't do that because I don't want to have to update two different tables with the same information. Thanks so much for your help though. I really appreciate it.

  4. #4
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    That didn't make any sense... Go have a search for "recursion"...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2
    Hi anejoh

    I thing I understand what you are after, but I have a suggestion.

    Why not put the SupervisorID combo on the main form (with a record source based on the on tblMain (or self-join) with bound column as ID.

    Then base the subForm also on tblMain with the link fields as frmMain.SupervisorID to sfrmSupervisor.ID.

    This way all the information on the subform will be shown details for the supervisor selected in the main form.

    The part I have not followed is which Null/Not Null records are required, but this I believe can be resolved in the row source used for the supervisorID comboBox using the self-join you have illustrated, but I could be wrong.

    I am not sure how tblMail.ID can be 'null' if it is the PrimaryKey (?) - unless you use outerjoins !

    Is that anything like you want !!

    Of course there could be other considerations that you have not mentioned
    which will blow the above out of the water !

    Sorry got to go (its a work think).


  6. #6
    Join Date
    May 2006
    Thanks MTB, it worked.

Posting Permissions

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