Copying a record and all of it's related records

[mswarner]

I have a main table with 3 different tables related to it. I'm using them in a form with 3 sub forms. I'd like to add a copy button that copies the record from the main table as well as the related records in the other tables. The key between the records is an autonumber field.

The copy should retain the relationships in the new records. I'd also like to prompt the user for certain info to update in the new main record.

Any help would be appreciated!

MRS

 

[Joe4]

Could you explain a little bit about what these tables are and the relationships between them? And what you are trying to do by copying the information from one table to others?

It is difficult to tell based on what you have provided so far, but there may be some designs flaws with your database, as you shouldn't have hte same data repeated in multiple tables if your all your tables are in Normal form.

 

[mdmilner]

Speaking very generally, you'll have to do this:

1) Identify the specific record. Most likely candidate, the Autonumber field as a unique key.
2) Copy the primary table record values and paste them into the destination table.
3) Copy each of the secondary table record values (related records) and paste them into the appropriate table locations

Step 3 must be done after Step 2 if you are to maintain referential integrity in the destination tables.

Note: You will only be able to copy the autonumber_keyfield value into a regular number field. You can't paste values into an autonumber field (for the destination table)
-
I have a suspicion I know what you're trying to do.
You're copying records to a set of working tables, possibly for use by somebody else. Almost sounds like you've got a supervisor distributing work to others.

If yes, jmiskey is 100% correct about his design flaws comment.
Still working on an assumption, one way you could do this (instead of copying records) is by flagging records in some fashion instead. Use a Boolean field that is by default set to no and instead of copying records set it to True or Yes.

This has the advantage of not duplicating data or requiring you to overwrite the original data.

From here you'd probably open the table as a filtered recordset (filter parameter would be the boolean field=True)

Mike

 

[mswarner]

Thanks guys!

The main table is a business requirements table which holds basic info on a particular business requirement. The three related tables are

1) Tests associated with the requirement
2) Technical documentation associated with the requirement
3) Changes to the requirement

Each subtable is related to the main requirement based on the autonumber of that requirement, but that is the only piece of info shared between the tables.