Automatically copying main and related sub records

[Stephen Jame Pattenden]

I am trying to create a macro/vba that will automatically copy a master
record and its related sub records into a new master record with the same
related sub records.

Automatically copying the main record is simple, but being able to copy all
the related sub records and pasting them into a new main record - to which
they would then be related is proving to be quite difficult.

Any suggestions would be greatfully received.

 

[Ken Snell \(MVP\)]

To copy the subrecords, you'll need to know the original master record's
primary key value, and the newly copied master record's primary key value.
Then you run an append query that uses the original master record's primary
key value in the WHERE clause for selecting the subrecords, and that uses
the newly copied master record's primary key value in the VALUES (or SELECT)
clause for the new subrecords' foreign key field.