Trap for autonumber ~ deluxe problem

[Ann in CA]

Hello,
I have a dilemma. I am trying to clone a record, and the records that fall
underneath it. I understand how to copy a record, trap the autonumber, and
use the autonumber in adding the many parts of the one to many records that
fall underneath it. Where I get lost is trying to double that problem.
For example:
Copy record ID 135 from table A to record 246.
In table B, records 1, 3, and 5 with FK 135 were copied to records 2, 4, and
6 with FK 246.
In table C, I need records 91 and 92 with FK 1, 93 with FK 3, and 94 & 95
with FK 5 copied to have FKs of 2, 4, and 6, respectively.

I have tried thinking of this many ways but I am absolutely baffled...Any
inputs would be greatly appreciated =)

 

[Allen Browne]

Here's one solution, Ann:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

 

[Ann in CA]

Hi Allen,
That's where I got the first half of the solution. But how do I get the
subrecords of the records on the subform? i.e, if I were to have a form, a
continuous subform, and a continuous subform (datasheet) on the subform? (The
subform isn't visible on the form, but I think that's besides the point)

 

[Ann in CA]

I got this figured out, but in a rather convoluted way--if you have an
elegant solution I would much prefer it.

In a nutshell, I opened a recordset with all of the values from the third
table joining the second table where the main ID for the first table ID in
the second table matched the original value, and for each one did a lookup in
a query looking up the new second table autoID, and then inserted a row using
the values from query 1 and query 2 to table 3...

 

[Allen Browne]

So for each record in the top-level form, you OpenRecordset(), AddNew each
related record (with Update), and Exeute the SQL statement to assign the 3rd
level related records.

That's a perfectly valid approach.