Carrying change of Employee ID Number to several tables automatically

[cft]

Access '97 - Database is used to accumulate performance data on Employees. Some are Contract (temporary) and others Permanent. If a Contract employee is hired, they receive a different and permanent EmpID. There is a form that allows a supervisor to change the ID number and update the Employee Data Table. However, changing the TblEmployee record does not change the EmpID records in other tables and keeps those records attached to the old Temporary ID that existed prior to the update. The queries and reports will only pull up the data according to the EmpID that was used at the time the record was created. This causes me to have to go into the db to change the ID# in each table using "Edit/Find-Replace All" to make the corrections. I need to fix this so the change of ID# in the one table retroactively changes the Temporary ID in all the tables to the new EmpID . If there are a number of employees converted to permanent in a short time frame, this causes problems in updating the tables as well as temporary problems in reporting accurately. Any suggestions to correct this and automate it at the time the supervisor makes the change when updating the Emp ID will be greatly appreciated. There are 5 other tables that use the EmpID to create records.

 

[cft]

Hi Pat,
Thank you for the information re: this. The reason the Sup changes the ID is because the "Contract"ed employees are acting Customer Service Agents who move Temp-to-Perm if they meet certain criteria and are approved during their probationary period for permanent hire. Once they are assigned to a Team, that team's Sup updates the db with their new and permanent ID replacing the Temporary ID assigned to them upon arriving.
I think that you have given me enough to work with here. Always learning, and having had no real formal guidance other than an intro class to Access, I find I can do some of the more complicated stuff and work-around things when I miss some of the basics.
Thanks again!