Allow Edit to Linked Field to Propogate to Subform

[caddcop]

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.

[ssanfu]

First, I want to say "TEST THIS ON A COPY - not the production database"!!!!

Again - do not try this on the real database!!!!!

When you feel comfortable with making the changes using either method, make the changes to the Production database.

MAKE SURE YOU HAVE A BACKUP of the original database!!!!
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------


Method 1
------------

Use "Cascading Updates".

In the mdb that holds the tables, open the relationship window. Make sure that the ONE table is linked to all of the MANY tables.
Double click on each link. Ensure that "Enforce Referential Integrity" has been checked.

If so, then for each link, check "Cascade Update Related Fields". Save changes.

Create a new mdb. This will be the Front end (FE). Link the new FE to the mdb that has the tables (the back end - BE).

Create a form that has a record source of the ONE table. The only field you really need is the PK field. Add any other fields to be able to identify the record(s) you want to change.

Make the changes to the linking (PK) field. The changes will cascade thru the "Many" tables.

See the attached MDB (zipped). You will need to relink the Test FE and BE.

After the changes have been made, BE SURE the uncheck the "Cascade Update Related Fields" in the Production database.



Method 2
------------

Remove "Enforce Referential Integrity" from the links between the "ONE" table and the "MANY" tables.

Create update queries for each of the tables. Run the queries for the "MANY" side tables first, then run the query for the "ONE" side table.

When all changes have been made, set "Enforce Referential Integrity" again for each of the links.

[pkstormy]

I religiously use the 'Find Duplicates' and 'Find Unmatched' wizard for creating a query to find orphaned or bad records. Fixing them though is a bit tougher if you're dealing with thousands of records. I'd try ssanfu's code.

[caddcop]

I decided to use an update query to keep things synchronized. I did not want to change the properties of the tables or their fields in case that would negatively effect the commercial product's function.