I have a database that is being used for a commercial product. certain tables are linked by fields that are not autonumber and unfortunately, many of these need to be edited.
The commercial product handles this type of change, but I am working on a tool for other staff to use to modify the data.
I have created a tabbed form and through the various subforms, at least four tables are linked. There is a master "job" table, an "additional job info" table, a "job staff" table and a "job companies" table. All of the non-master tables are a many to one relationship with a "code" field to the master "job" table. The code field is a complex string of numbers, letters and characters based upon a system that was devised long ago and this approach cannot be changed.
However, many of these codes were not entered correctly and need to be revised. However, since this field is the linking field, I need to change all related records is all of these tables when the master record is revised.
Currently, if the master code is changed, the other tabs and subforms appear to retain their data. But if you navigate to a new master record, those subform records become orphaned. I need a way to detect that the master record was changed and to propagate those changes to all of the other tables.