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



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

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


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.


Jeff Boyce
Microsoft Office/Access MVP

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