Tags: access, autolookup, awards, colleagues, database, dontknow, holds, ive, local, mdb, microsoft, mysql, oracle, sql, struggling, table, twenty-odd, underlying, values, writes

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

On Database » Microsoft Access

4,201 words with 1 Comments; publish: Thu, 05 Jun 2008 11:35:00 GMT; (25062.50, « »)

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

mad.ms-access.todaysummary.com.umich.edu

All Comments

Leave a comment...

  • 1 Comments
    • 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

      "Mark Davis-Craig" <Mark Davis-Craig.ms-access.todaysummary.com.discussions.microsoft.com> wrote in

      message news:4BB9DA87-71E2-466B-B231-682294E78685.ms-access.todaysummary.com.microsoft.com...

      > 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

      > mad.ms-access.todaysummary.com.umich.edu

      #1; Thu, 05 Jun 2008 11:36:00 GMT