Duplicate the record in form and subform and Subbforms within Subf

[alathwell]

I would like to duplicate records in a setup as decribed below:

My form consists of 3 levels with a Main Form -> 1 Subform1 > & a further 4
subforms under Subform1.

I have sucessfully duplicated records using VBA SQL statements - supplied by
Allen Browne - for a Form, Subform, set up, but I cannot get the code right
for 3 levels.

Please help

Regards

alalthwell

 

[Allen Browne]

The code at:
http://allenbrowne.com/ser-57.html
uses a recordset to duplicaete the main form record (so you can get the new
primary key value), and then uses the key value in an Append query string to
add the related records.

You have another layer in the middle, so you will need to use a recordset
for the middle level also (to get the new key value there also, for use in
the Append query at the lowest level.)

This means a bit extra coding. Essentially you have a loop within a loop, so
the logic is:

For each record in the main form
Add a new main record (and get the key value)
For each record in the middle form
Add a new main record, and get the key value
Execute the Append query to create the sub-sub-records
Next record in the middle form
Next record in the main form.

If you have further levels of subform, you have more 'middle' layers here.

 

[alathwell]

Thanks for the suggestion. I thought this would probably be the sort of
approach I would need to adopt and your confirmation has cleared my thoughts.
I am not great with SQL so this has really helped.

Thank you very much.

Regards

Andrew Lathwell