Copying table records

[Guest]

I have a table (we'll call it "Depo") that has two other tables (we'll call
them "Deds" and "Sprds") linked to it. The tables are linked by the field
'DepoNum' in the first table. I want to make a copy of one record in "Depo"
and corresponding copies if the linked records in "Deds" and "Sprds". What's
the best way to do this?

TIA

 

[Guest]

A query will pull the data but what are you planning to do with the copy once
you have it? You can not paste it back in the tables as I expect that
DepoNum is a key field meaning it must be unique.

 

[Guest]

Karl,
My thinking is to do a "make table" query to create a separate table with
a single row in it then do an "appens" query to add it back in. I think the
append query will change the DepNum field since it is an autonumber field.
The problem then is to copy records in the linked tables that are linked to
the first one.

The purpose of all this is to allow the user to create a copy that they can
then modify without affecting the original records.

 

[John Vinson]

Karl,
My thinking is to do a "make table" query to create a separate table with
a single row in it then do an "appens" query to add it back in. I think the
append query will change the DepNum field since it is an autonumber field.
The problem then is to copy records in the linked tables that are linked to
the first one.

The purpose of all this is to allow the user to create a copy that they can
then modify without affecting the original records.

That's overkill. You can run an Append query from a table back into
that same table. Just don't include the DepNum field in the query -
copy all the other records and let the Autonumber assign a new DepNum
value to the new record.

John W. Vinson[MVP]

 

[Guest]

Thanks, John! That's just the key I needed to unlock the puzzle. I did the
append query to the "Depo" table without the DepNum field then used the DMax
function to get the new DepNum. With the original DepNum value I was able to
do an append query on each of the other two tables and update the NepNum
field to reflect the new DepNum. It works like a charm!

Thanks again to both of you for your ideas.