Generally you would reference the primary key of the same table for this,
e.g. you might have an Employees table with a primary key EmployeeID and a
foreign key SupervisorID which references the EmployeeID column. A combo box
bound to the SupervisorID column, but set up to show the names would have a
RowSource such as:
SELECT EmployeeID, FirstName & " " & LastName
AS FullName
FROM Employees
ORDER BY LastName, FirstName;
Its BoundColumn property would be 1, its ColumnCount 2 and its ColumnWidths
something like 0cm;8cm (this will convert automatically to inches if entered
on a system using imperial measurements; the key thing is that the first
dimension is zero to hide the first column).
If the Employees table has a column which indicates which employees can be
supervisors then you can limit the combo box's list by adding a WHERE clause
referencing this column to the control's RowSource property SQL statement.
If you do use a separate Supervisors table this should have only the
EmployeeID column and not repeat the names or other attributes which are in
the Employees table, as that would introduce redundancy. You can then join
the tables as the RowSource of the combo box:
SELECT Supervisors.EmployeeID,
EmployeeID, FirstName & " " & LastName
AS FullName
FROM Employees INNER JOIN Supervisors
ON Supervisors.EmployeeID = Employees.EmployeeID
ORDER BY LastName, FirstName;
Incidentally, if you do use a Supervisors table a combo box for entering
rows into this table would be set up exactly as the first one above.
A separate Supervisors table would be advisable if there are attributes of
supervisors which do not apply to other employees as these would be
represented by extra columns in the Supervisors table. What you'd have in
fact would be a Type Employees and a Sub-type Supervisors, and the correct
way to model this is by two tables where the primary key of the latter is
also a foreign key referencing the primary key of the former, both EmployeeID
in this case. Employees would still have a foreign key SupervisorID of
course referencing the primary key of Supervisors. As this references the
primary key of Employees the self referencing which existed when a single
table is used still operates therefore.
Ken Sheridan
Stafford, England