Cloning of records

[Pwyd]

What is the easiest way to clone a record's main, and subform contents and
copy them over to a new record, and could you explain the advantage of each
method proposed?

 

[Tony Toews [MVP]]

What is the easiest way to clone a record's main, and subform contents and
copy them over to a new record, and could you explain the advantage of each
method proposed?

Hmm, this sounds like a homework question.

Tony

 

[Pwyd]

It isn't. I asked for an explanation because i would like to decide on the
method based on the merits that i consider most important rather than what
others do. Its for my 1042 compliance database for the new taxes the IRS has
decided to actually enforce this year.

 

[John W. Vinson]

What is the easiest way to clone a record's main, and subform contents and
copy them over to a new record, and could you explain the advantage of each
method proposed?

That depends considerably on your table structure and relationships. Of course
the data isn't on the form or the subform - it's in tables; the most
straightforward way to move data would be with a series of Append queries.
These will need somewhat different design depending on whether you have
autonumber primary keys or natural keys (or, heaven forbid, no primary keys at
all), on whether you need to copy all fields or only a selection, etc.

 

[Pwyd]

Good points, all. A set of append queries was actually what i had in mind.
Are there any requirements for such a query to run, such as, the table not
being open elsewhere?

The tables in question do have an autonumber primary key. Why would this
affect an append query though?

 

[John W. Vinson]

Good points, all. A set of append queries was actually what i had in mind.
Are there any requirements for such a query to run, such as, the table not
being open elsewhere?

It'll run... maybe... but it would be a lot safer if you had the database open
exclusively.

The tables in question do have an autonumber primary key. Why would this
affect an append query though?

If you have a record in your target table with an autonumber primary key with
a value of 123, and you try to append a record with a value of 123, the append
will fail with a Key conflict error.

Or if you have a primary key with a value of 123 linked to several child
tables, with long integer foreign key values of 123, you'll need to figure out
how to assign a new unique key value in ALL the child tables. Not impossible
but not trivial!