(*.mdb) Autolookup that writes values to the underlying table

[Mark Davis-Craig]

I've been struggling for about ten hours now and my local colleagues don't
know either.

I have a table called Awards. It holds twenty-odd fields related to special
compensation for our employees. Right now it's pretty much a stand-alone
table.

I have another table which stores info about our employees. You can imagine
its fields.

On the Awards table, we want to know the current job and current department
of the employee so we want those fields exist in the Awards table.

I'm trying to create a form for data entry into the Awards table such that
an Autolookup on the foreign key for the employee ID or primary key on the
Employees table will not only display employee name, current department,
current job, etc. in the pick box and in the form but will also actually
write the employee name, department, and job into the Awards table. I have
the autolookup working well, but the data from my Employees table never gets
written into my Awards table.

I know this may go against many principles of RDBMS design, but we really
don't care to update the department or job title of the employees once
they're on the Awards table. We want to be able to report on their job at
the point in time they received the award. Their job could easily change in
the future.

Thanks for any assistance.

Mark Davis-Craig
(e-mail address removed)

 

[Jeff Boyce]

Mark

So you're saying that selection of an employee (from a combo box) on an
Awards form should write a record to the Awards table? Are you sure? And
if someone like me with fat fingers picks the wrong person, do you want to
be able to remove what was automatically written?

Another approach would be to have the Awards form include a <Save> button,
and hold off writing the Employee's current job & department until the form
has confirmed that all required fields are filled (by using the BeforeUpdate
event of the form to validate what's been entered).

I assume you have a "lookup" table of departments, and maybe "jobs", too.
You'd use this in the Employee table to save having to re-type the same
"job" and/or "department", and you'd use it in the Awards table to reflect
which job and department the employee (EmployeeID) held when awarded.

Regards

Jeff Boyce
Microsoft Office/Access MVP