Suzie:
If you are looking up names from another table, then you may well want
to use a combo box on your form so that the user can simply select a
name from the list. You should not store the names in both tables,
however, only a foreign key which references the primary key of the
table with the names.
I can probably best illustrate it with a simple example. Lets say
your form is based on a Projects table and each project has a project
leader, whose names are in a row in an Employees table as FirstName
and LastName columns (fields). The primary key of the Employees table
will be EmployeeID, most probably an autonumber column. In the
projects table you'd have a ProjectLeaderID column as a long integer
number column (NB: not an autonumber in this case). On your form
you'd add a combo box set up as follows:
ControlSource: ProjectLeaderID
RowSource: SELECT EmployeeID, FirstName & " " & LastName FROM
Employees ORDER BY LastName, FirstName;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access
will automatically convert the last one. The important thing is that
the first dimension is zero to hide the first column and that the
second is at least as wide as the combo box.
You'll see the names in the combo box's list and when you select a
name you'll see it in the control as the project leader, but the
underlying value of the control and therefore the ProjectLeaderID
column in the table will be the numeric EmployeeID value corresponding
to the name of the employee.
One thing you need to be careful of with names, however, is that they
can be duplicated, even in small workgroups (I worked with two Maggie
Taylors), so listing just the names might not be enough, in which case
you'd need to add another column to the combo box's RowSource, such as
JobTitle, to distinguish them and change its ColumnCount property to 3
and its ColumnWidths to something like 0cm;4cm;4cm (you'd experiment
with the last two dimensions to get the best fit, but the first must
be zero).
Another important point with a scenario like the example above is that
a relationship must be created between the Projects and Employees
tables on the ProjectLeaderID and EmployeeID columns, and referential
integrity must be enforced. This both prevents a non-existent
employee being made a project leader, and also prevents a row being
deleted from the Employees table while that employee is still recorded
as project leader in a row in the Projects table. This means of
course that if an employee leaves and you wish to delete the row from
employees, you either have to assign new leaders to that employee's
projects, or delete those rows from the Projects table before the
employee's row can be deleted from Employees. A more likely course of
action in most organisations would be that the employee's row in
projects would be retained, but they'd be shown as having left, e.g.
by entering a value in a DateQuit column. That way 'historic' project
records can still be maintained in the database with whoever was the
project leader at the time.
Ken Sheridan
Stafford, England
