Duplicate record and child records

[andrewg]

I know most people want to eliminate duplicate records, however i need
to create them!

I have a Access 2003 database which has a Form we'll call orders.
There are 10 subforms linked to orders through Order_ID. There are of
course 11 tables behind these forms.

I'd like to be able to create a duplicate of a record and all the
associated records in the linked subforms/tables.

I've read it possible with an append query, but I'm wondering if there
is a way to filter a recordset and then append the recordset back to
the original table. 11 recordsets, 11 filters and 11 appends as
opposed to 1 huge dirty query which would be harder to debug in my
opinion but i'm more than open to advice and options here.

 

[Allen Browne]

No. You need to duplicte the main record in a way that gets you the newly
generated key value, and then use that value in the Append query statements
to duplicate the related records in the other 10 tables.

Example at:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html
The example shows how to handle the main record and related records in one
table. You will need to do the other 9 related tables in a similar way.

 

[andrewg]

No. You need to duplicte the main record in a way that gets you the newly
generated key value, and then use that value in the Append query statements
to duplicate the related records in the other 10 tables.

Brilliant!! Thanks so much.

At the moment i'm getting "error in 'Insert Into' statement" on this
query:

INSERT INTO Job ( Order_ID, StartDate, TakenBy, Referral, Subject,
Stock, Ink, Number, MixCost, [Material Cost], DateInvoiced, PayMethod,
PayReceived, InvNum )
SELECT 20 As NewID, StartDate, TakenBy, Referral, Subject, Stock, Ink,
Number, MixCost, [Material Cost], DateInvoiced, PayMethod,
PayReceived, InvNum FROM Job
WHERE Order_ID = 20;

The query builder highlights the column Number, is this a reserved
word i wonder??

 

[andrewg]

No. You need to duplicte the main record in a way that gets you the newly
generated key value, and then use that value in the Append query statements
to duplicate the related records in the other 10 tables.

Brilliant!! Thanks so much.

At the moment i'm getting "error in 'Insert Into' statement" on this
query:

INSERT INTO Job ( Order_ID, StartDate, TakenBy, Referral, Subject,
Stock, Ink, Number, MixCost, [Material Cost], DateInvoiced, PayMethod,
PayReceived, InvNum )
SELECT 20 As NewID, StartDate, TakenBy, Referral, Subject, Stock, Ink,
Number, MixCost, [Material Cost], DateInvoiced, PayMethod,
PayReceived, InvNum FROM Job
WHERE Order_ID = 20;

The query builder highlights the column Number, is this a reserved
word i wonder??

Never mind, it was reserved, [Number] fixed the issue.

Great solution!!

 

[Allen Browne]

Well done.

To make sure you don't get caught with reserved words in the future, here's
a list of the names to avoid:
http://allenbrowne.com/AppIssueBadWord.html

I use that list myself when creating tables if there is any question about
the field name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

No. You need to duplicte the main record in a way that gets you the
newly
generated key value, and then use that value in the Append query
statements
to duplicate the related records in the other 10 tables.

Brilliant!! Thanks so much.

At the moment i'm getting "error in 'Insert Into' statement" on this
query:

INSERT INTO Job ( Order_ID, StartDate, TakenBy, Referral, Subject,
Stock, Ink, Number, MixCost, [Material Cost], DateInvoiced, PayMethod,
PayReceived, InvNum )
SELECT 20 As NewID, StartDate, TakenBy, Referral, Subject, Stock, Ink,
Number, MixCost, [Material Cost], DateInvoiced, PayMethod,
PayReceived, InvNum FROM Job
WHERE Order_ID = 20;

The query builder highlights the column Number, is this a reserved
word i wonder??

Never mind, it was reserved, [Number] fixed the issue.

Great solution!!