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!
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 tables...one 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
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.
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 !