it does not change the existing records in the inventory's employee field.
And it shouldn't change the existing records. Why do you need it to? Why does adding a new employee record require such an update?
And it is better to link records by ID number than name. Employees can have the same name, they change their names and names are easily entered incorrectly. All of those issues can require needed to change employee names in related tables if you match records by name. But they are avoided if you use an ID number instead.
Thank you for your reply. I agree with your statement about using the ID. I had tried to do that but I was having a difficult time getting the drop down menu in my layout to display the names of the employees, but I have resolved that issue and now it is linked by ID not name.
Why does adding a new employee record require such an update?
I am not adding a record, but changing one. For example I have a manager that leaves and I replace him with someone new. I would like the inventory that the new manager is using to be linked to him/her and not the old employee. I have other people that maintain the inventory and use this database to do so. If they were not here the same time as the old employee they may not know where to find the piece of equipment and waste a lot of time looking, whereas if they see the new manager they know right where to go. I thought that changing the link to an ID not a name would fix it but it did not.
I am not adding a record, but changing one.
Surely you are adding a new record in the employee table for the new employee? If, instead, you were updating the existing employee record to have the new employee's data, there should be nothing to update except for updating the fields in the employee record.
But I would create that new employee record and then the existing records in inventory would need to be linked to this new employee record: From the employee table, Go to Related Records is a script step that can be used to produce a found set of related inventory records on a layout based on the inventory table. Replace Field contents can then be used to update the match field to the value of the new employee record's ID.
There are also ways to set up your database where your inventory matches to a record for a specific position, which in turn is linked to a specific employee. In such cases, you could just update a single position record to link to the new employee record.