Form - Update record, copy original

[sweeneytime]

Hi Guys,

First time post, new to access, I do VBA for excel. I am looking for some help please, the logic seems easy, but I may even have the wrong approach with my excel eyes on it. My access VBA books are in the post

I have a table of employees, I want to keep historic data along with live. Take John's salary, for every increase he will get a new record. I then want the old record to update the "Status" Field to "Inactive", so I can query by active/inactive.

My 'grand' plan - You highlight John's active record on a form, press a vba button,
-It changes the "Status" field on the current record to "Inactive"
-Make a copy of the original record with "Status" as "Active"

Then you can update John's salary and save.

Is this the correct way to approach the task? Is the code simple enough? All help appreciated.

Thanks,
Sweeneytime

 

[John Big Booty]

Here's a sample that demonstrates one way might perform this task.

 

[sweeneytime]

Your not just a funny username! Thats great, I've quickly gone through it and got the jist of it, vba calls seem quite easy when. Thanks a million for that! I'll spend a bit of time understanding the whole lot.

 

[sweeneytime]

Sounds good, so I would then write a query, which returns the MAX date for each employee?

Then one follow on question if I may, some employees have 2 "Active" salaries due to different cost centers. Would the query be, each employee by MAX date where cost centers are not equal?