Combo Box values different for each record in Datasheet view

[Guest]

Here is what I am stuck with, any ideas on which way to go with this would be
greatly appreciated. (for all of these, SSN is Social Security Number)

table1. I have a table with SSN, FirstName, LastName

table2. I have a table with SSN, Position, etc...

table3. I have a table with two columns for position to show the direct
reporting structure for our organization, i.e. EmployeePostion,
SupervisorPosition

table4. I have one last table that holds direct reporting information,
EmployeeSSN, SupervisorSSN


What I want to do is set up a form that will show in datasheet view two
fields. The first field would have all the employees names in the
organization (table1), and then the other field would be a combo box that
they can select the name of someone to be the direct report supervisor, and
this will put the SSN of the supervisor into the correct field in the 4th
table listed above. This much I can do without a problem, the issue is it
shows all the employees as possible direct reports (100's of names)

The trick is I want the combo box to only show names of those people, based
on the reporting structure (table3), that can be assigned to surpervise the
employee. This is where I am a little stuck and could use some advise. Is
this even possible? Ideas for a starting direction?


If you need any other information to understand where I am at with this let
me know. Thanks for any ideas you may have.

Thanks,
James Stephens

 

[DebbieG]

Add some criteria to the Row Source of your combo box to return only the
supervisors (SupervisorPosition = ??).

HTH,
Debbie


| Here is what I am stuck with, any ideas on which way to go with this would be
| greatly appreciated. (for all of these, SSN is Social Security Number)
|
| table1. I have a table with SSN, FirstName, LastName
|
| table2. I have a table with SSN, Position, etc...
|
| table3. I have a table with two columns for position to show the direct
| reporting structure for our organization, i.e. EmployeePostion,
| SupervisorPosition
|
| table4. I have one last table that holds direct reporting information,
| EmployeeSSN, SupervisorSSN
|
|
| What I want to do is set up a form that will show in datasheet view two
| fields. The first field would have all the employees names in the
| organization (table1), and then the other field would be a combo box that
| they can select the name of someone to be the direct report supervisor, and
| this will put the SSN of the supervisor into the correct field in the 4th
| table listed above. This much I can do without a problem, the issue is it
| shows all the employees as possible direct reports (100's of names)
|
| The trick is I want the combo box to only show names of those people, based
| on the reporting structure (table3), that can be assigned to surpervise the
| employee. This is where I am a little stuck and could use some advise. Is
| this even possible? Ideas for a starting direction?
|
|
| If you need any other information to understand where I am at with this let
| me know. Thanks for any ideas you may have.
|
| Thanks,
| James Stephens