change information in a record, but keep historic info

[Chris]

I have a personnel table linked to several databases. What I need to do is
update some of the personnel, ie. promotions, and have it keep the old data
with their old ranks and have their new information appear when selecting
their name from a combo box in a form. I have tried multiple drop down boxes
on the form, which allowed to select the new rank, but then changed back to
the old rank when the field was unselected.

 

[John W. Vinson]

I have a personnel table linked to several databases. What I need to do is
update some of the personnel, ie. promotions, and have it keep the old data
with their old ranks and have their new information appear when selecting
their name from a combo box in a form. I have tried multiple drop down boxes
on the form, which allowed to select the new rank, but then changed back to
the old rank when the field was unselected.

Then you need *two tables* - a table of current information (with the
PersonnelID as its primary key), and a history table with fields for
PersonnelID, EffectiveDate, and EndDate. Add a new record to the history table
using VBA code in the event which updates the personnel table.

Remember - forms *are just windows*. They don't store any data. Putting a
combo box on a form will not store any information anywhere!

John W. Vinson [MVP]