Copy/Paste Records with Multiple Subform Records

[Max Smart]

What's the best way to copy and paste records and include related subform
data. Should I use DAO to create the new records and then open the form to
the new record? Or is there an easier way? I just need a general clue - I
can probably hammer out the details myself.

Thanks,
Max

 

[Allen Browne]

Use a combination of DAO and SQL.

Duplicate the main record with AddNew to the form's RecordsetClone.
You can now get the new autonumber.

To duplicate the related records, create a string that is an Append query
statement. The WHERE clause will pick up the records related to the main
form record, but the value for the foreign key will be the new autonumber
value picked up above.

Finally, show the new record by setting the main form's bookmark to
..LastModified.

Before you start, check that the form's Dirty property if False, and
NewRecord is False.

 

[Max Smart]

Thanks for the tip. I'll get started on it this weekend.

Max