Lookup Field Update Problem

I have one table called tblEmployeeSalary inside that table I have a lookup
box based off a table called tblSalarySchedule in the table holds all of my
salary info fo r each of my positions and their Annual Salaries. My intent is
to assign a salary to an employee so I can use it to figure out my employees
pay check in a table called tblPayDayHours. The problem I am having is that I
can use the Salary value in a query for all of my calculations but if I
change the amount of a salary in the tblsalaryschedule it also changes the
value in past transactions in tblPayDayHours. This wont work if I am trying
to keep a record of what I have paid my employees for the whole year. Is
there a way to save the value from the lookup box in tblPayDayHours and not
have it update if I change tblSalarySchedule? Please keep it simple I am
still learning!!!

On Tue, 16 Mar 2010 14:34:04 -0700, jwebster1979
wrote:

I have one table called tblEmployeeSalary inside that table I have a lookup
box based off a table called tblSalarySchedule in the table holds all of my
salary info fo r each of my positions and their Annual Salaries. My intent is
to assign a salary to an employee so I can use it to figure out my employees
pay check in a table called tblPayDayHours. The problem I am having is that I
can use the Salary value in a query for all of my calculations but if I
change the amount of a salary in the tblsalaryschedule it also changes the
value in past transactions in tblPayDayHours. This wont work if I am trying
to keep a record of what I have paid my employees for the whole year. Is
there a way to save the value from the lookup box in tblPayDayHours and not
have it update if I change tblSalarySchedule? Please keep it simple I am
still learning!!!

First off... most of the experts dislike Lookup Fields. See
http://www.mvps.org/access/lookupfields.htm for a critique.

Secondly, you need to have a Salary field in tblSalarySchedule (for the
*current* salary), and you ALSO need a Salary field in tblPayDayHours (for the
salary as of the date of that paycheck). If you use a Form (as you should be
doing), you can have a combo box selecting the employee ID, and use a line of
VBA code or a macro to "push" that employee's current salary into this new
field.
--

John W. Vinson [MVP]

Would it if I listed the salaries in a combo box instead? Would it still
update those earlier transactions that took place earlier in the year? Also
any suggestions on which macro to use to push the salary in the form?

"John W. Vinson" wrote:

On Tue, 16 Mar 2010 14:34:04 -0700, jwebster1979
wrote:

I have one table called tblEmployeeSalary inside that table I have a lookup
box based off a table called tblSalarySchedule in the table holds all of my
salary info fo r each of my positions and their Annual Salaries. My intent is
to assign a salary to an employee so I can use it to figure out my employees
pay check in a table called tblPayDayHours. The problem I am having is that I
can use the Salary value in a query for all of my calculations but if I
change the amount of a salary in the tblsalaryschedule it also changes the
value in past transactions in tblPayDayHours. This wont work if I am trying
to keep a record of what I have paid my employees for the whole year. Is
there a way to save the value from the lookup box in tblPayDayHours and not
have it update if I change tblSalarySchedule? Please keep it simple I am
still learning!!!

First off... most of the experts dislike Lookup Fields. See
http://www.mvps.org/access/lookupfields.htm for a critique.

Secondly, you need to have a Salary field in tblSalarySchedule (for the
*current* salary), and you ALSO need a Salary field in tblPayDayHours (for the
salary as of the date of that paycheck). If you use a Form (as you should be
doing), you can have a combo box selecting the employee ID, and use a line of
VBA code or a macro to "push" that employee's current salary into this new
field.
--

John W. Vinson [MVP]
.

On Tue, 16 Mar 2010 18:31:02 -0700, jwebster1979
wrote:

Would it if I listed the salaries in a combo box instead? Would it still
update those earlier transactions that took place earlier in the year? Also
any suggestions on which macro to use to push the salary in the form?

A combo box is *just a display tool*. It's not a data repository!!! You must -
no option, no choice - store your data in Tables; and if you have two
different "salary" attributes - "Current Salary" and "Salary at a past point
in time" - they must be stored in separate places.

You'll need to write the macro yourself but it's easy - use the SetValue
action (you'll need to use the Show All Actions button in 2007 to see it in
the AfterUpdate event of the salary combo box, to set the value of some other
bound control (a textbox would be fine) to the salary chosen using the combo
box.
--

John W. Vinson [MVP]

I think I understand, I can use the combo box to choose the salary and then
use the macro to store it as Hard Data in my table under a current salary.
Thankyou for all of your help!!!!

"John W. Vinson" wrote:

On Tue, 16 Mar 2010 18:31:02 -0700, jwebster1979
wrote:

Would it if I listed the salaries in a combo box instead? Would it still
update those earlier transactions that took place earlier in the year? Also
any suggestions on which macro to use to push the salary in the form?

A combo box is *just a display tool*. It's not a data repository!!! You must -
no option, no choice - store your data in Tables; and if you have two
different "salary" attributes - "Current Salary" and "Salary at a past point
in time" - they must be stored in separate places.

You'll need to write the macro yourself but it's easy - use the SetValue
action (you'll need to use the Show All Actions button in 2007 to see it in
the AfterUpdate event of the salary combo box, to set the value of some other
bound control (a textbox would be fine) to the salary chosen using the combo
box.
--

John W. Vinson [MVP]
.