Firstly, don't store the other values from the referenced table in fields in
the referencing table. This introduces redundancy and the possibility of
inconsistent data. Store only the primary key value from the referenced
table as a foreign key field in the referencing table. Lets say you want to
record which employee undertakes a sale in an Orders table, but on the Orders
form want to show the employee's first and last names and their job title,
which are fields in an Employees table. In the Orders table you'd have a
numeric EmployeeID field for this (don't use names as keys; they can be
duplicated - I once worked with two Maggie Taylors in the same office). Add
a combo box, cboEmployee say, to the Orders form with its properties as
follows:
ControlSource: EmployeeID
RowSource: SELECT EmployeeID, FirstName, LastName, JobTitle FROM
Employees ORDER BY LastName, FirstName;
BoundColum: 1
ColumnCount: 4
ColumnWidths 0cm;3cm;3cm;4cm
ListWidth: 10cm (i.e. the sum of the column widths)
If your units of measurement are imperial rather than metric Access will
automatically convert them to inches if entered in metric units The
important thing is that the first dimension of the ColumnWidths property is
zero to hide the first column. You'd need to experiment with the other
dimensions to get the best fit in the drop down list.
When you select an employee from the list the combo box will show the first
name. To show the last name and job title add two unbound text boxes to the
form, and as their ControlSource properties reference the Column property of
the combo box:
=cboEmployee.Column(2)
and
=cboEmployee.Column(3)
Note that the Column property is zero based, so Column(2) is the third
column (LastName) and Column(3) the fourth column (JobTitle). The user never
sees the EmployeeID of course as its function is purely to provide an
arbitrary unique identifier behind the scenes.
BTW tables don't have 'cells'. That's a spreadsheet concept. Tables have
rows and columns (aka records and fields).
Ken Sheridan
Stafford, England
