As you have it set up you have a one-to-many relationship between the
Employee and Clients tables, which would mean you have a situation such as
each employee being responsible for one or more client account, but each
client account being handle by only one employee. Is that your real world
scenario?
If so then the foreign key employee id column in Clients references the
primary key of Employee and each row would contain the value which matches
that of the row in Employee for the employee handling that client account.
In a form based on the Clients table you would use a combo box bound to the
employee id foreign key column. The value of this control would be the
employee id, but the user does not need to see that as its an arbitrary
number with no meaning per se. What they need to see is the name of the
employee. This is done by hiding the first column returned by the combo
box's RowSource property so it shows just the name. As the name is made up
of two columns first name and last name you can concatenate these into the
full name in the combo box. So the RowSource property of the combo box would
be:
SELECT [employee id], [first name] & " " & [last name] FROM employee ORDER
BY [last name], [first name];
The other properties of the combo box are set as follows:
ControlSource [employee id]
BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first, employee id, column, so only the
name shows. In fact if you enter the dimensions in centimeters Access will
automatically convert them to inches if its set up to use imperial
measurements)
When you select a name form the list the value of the control, and therefore
of the underlying employee id column to which its bound, will be the value of
the employee id column in the employee table which corresponds to the name
you select.
Ken Sheridan
Stafford, England
Michael B said:
I am new to Access and have recently taken a 2 day course. I am having a
problem. So... I have created an employee table with employee id (auto
number), last name, first name, hire date and birth date. I have created
another table called clients that has a client id (auto number), last name,
first name, employee id (number) and some other fields.
I have created a form that has all of the client information including the
employee last name and first name, but not the employee id. How do I get the
client table to recognize the employee id?
