Editing incorrectly entered records on a form

[kickergirl]

I have a database created in Access 2000 that has one form (DataEntryfrm) with one subform (Trainingsubfrm). The main form is linked tblParticipant (no duplicates allowed) and the subform is linked to tblTraining (duplicates allowed). The primary key is SSN. Referential integrity has been placed on the tables with cascading updates and deletions.

The premise is to collect the number of trainings attended by an individual. A search is performed when a SSN is entered. And if a person has already been entered into the database, the previously entered information will appear in the form.

The problem.... People are incorrectly entering SSNs and discovering it after they type in the correct SSN and a match does not occur. How do they then edit the incorrect record and have all of the related information connect to the correct record?

[jeffstl]

If you used the SSN as your unique identifier and that is what ties the tables together your not going to like the answer.

You have to go through all the records of your subform table and change the SSN back to the correct one if you want to link them with the proper SSN.

Obviously creating a new record with the correct SSN wont "automatically" link the old records with the incorrect SSN to them. You have to manually (or via a script) update all the subform records with the corrected SSN.

To avoid this in the future you should link your main table and your sub table with a autonumber key or some other identifier.....not the SSN. This way the SSN can change, but the linkage between the records will still be ok.

That way if someone (through trial and error searching for a mis-type) finally corrects thier SSN to the right one on the main record, all thier sub-form records are still linked to the main one.

[mshmyob]

Before you try and fix the data anomolies you should consider designing your table structure properly.

I am not sure if you said both tables have SSN as the primary key but if you did then this is incorrect. 2nd you said that the one table doesn't have a proper primary key. Primary keys cannot allow duplication.

I assume your one table has SSN as the primary key (this is unique) and your foreign key in table 2 is SSN and this allows duplication. You need a primary key in table 2 that does not allow duplication.

If you do it correctly when table 1 has a SSN entered it would automatically populate SSN in table 2 and not allow it to be changed.

If you need help redesigning your table structures please let us know more details of what you need.

cheers,