Forms linked to table


G

Guest

I have a form that is used as an input sheet to update a table. i want to
use a combo box on the form used to input data onto the table to eleminate
error. For example, one of the input fields is "Supervisor"- I have created a
table with just the supervisors names and added a combo box to the form, but
the combo box says unbound and does not translate onto the table. Is that
possible?
 
Ad

Advertisements

G

Guest

It should be possible. In your table you should have a field supervisor
(probably an ID field). In your form set the controlsource of the combo to
that field and the rowsource of your combo to the supervisors table. That way
the combo is no longer unbound.

hth
 
Ad

Advertisements

G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top