duplicating records that contain subforms

[John Harrington]

In my database, it is convenient to occasionally duplicate a record
and modify it.

The button wizard in Access helpfully has a "Duplicate record"
option. Unhelpfully, it won't duplicate a record's relationships.

I would like to create a function that duplicates a given record and
its relationships.

For example (this is purely an example and not designed to be
practical):

Suppose I have a table of names of people and their ages (tblPeople).
I have another table with books (tblBooks). In addition, I have a
junction table that associates people with their favorite books
(tblFaves).

Would someone show me a function that takes an ID from tblPeople as
input, duplicates that record in tblPeople and then duplicates the
associations in tblFaves?

For example (this is also purely an example and not designed to be
practical):

If tblPeople ID 1 is:

[ID]: 1, [firstname]: John, [lastname]: Smith, [age]: 32

And tblFaves records that John Smith likes tblBooks ID 1 (Moby Dick)
and 7 (War and Peace).

[peopleID]: 1, [bookID]: 1
[peopleID]: 1, [bookID]: 7

Then if I pass 1 to function Duplicate, I get a new tblPeople ID n:

[ID]: n, [firstname]: John, [lastname]: Smith, [age]: 32

Additionally, in tblFaves I get two new reocrds:

[peopleID]: n, [bookID]: 1
[peopleID]: n, [bookID]: 7


Thanks,
John

 

[Jeanette Cunningham]

Hi John

have a look at what Allen Browne has here

http://www.allenbrowne.com/ser-57.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John Harrington]

Hi John

have a look at what Allen Browne has here

http://www.allenbrowne.com/ser-57.html

Thanks, Jeanette. That looks like what I'm looking for.