duplicate row, related rows, and their related rows

[Scott Parmelee]

Hi,
I have a table, that has a related table with "child" rows, and that table
has two more related tables. Each table as an auto-number as the primary
key and relations set up between the tables.
I would like to duplicate a row and all child related rows. I have seen
Allen Browne's excellent documentation and sample at
http://allenbrowne.com/ser-57.html, but that only goes one level deep. I
then saw an older newsgroup posting where he discussed deeper levels and
advised:

"If you have a 3rd relational level:
1. Use DAO to create the first level duplicate and get the new ID.

2. Use DAO in a loop to create the 2nd level duplicates, using the
ID from step 1 as foreign key, and getting the new primary key.


3. Within the loop, Execute an SQL string that employes the primary
key from level 2 as the foreing key for level 3. "

but I'm getting confused at this point. I'm not sure at step 2 what I
should be looping through, a recordsetclone of the subform perhaps? Does
anybody have any handy examples?
Thanks,

Scott

 

[Allen Browne]

Hi Scott. The key concept is that you need the primary key value of the new
record you added to your main table to create the related records. If you
have 3 tables to duplicate:
Main -> Middle level table
Middle -> Lower level table
then you will need the primary key of *each* record you add to the middle
table, so you can use that value to create the records in the lower level
table.

The example below is untested aircode: really just a quick demo of the
concept. Hopefully it's enough in conjunction with the article you
referenced. Again, we assume that the record to be duplicated is the one in
the form (and as per the original article you need to check that there is a
record, and it is saved.)


Dim db as DAO.Database
Dim rsSource As DAO.Recordset 'Source records
Dim rsTarget as DAO.Recordset 'Table to append records to.
Dim rsClone as DAO.Recordset 'Main form's recorsetclone.
Dim strSql As String
Dim lngMainID as Long 'Main table's primary key
Dim lngMiddleID As Long 'Middle table's primary key

'**********************************
'Add the main record to the form's Clone set
'**********************************
Set frmClone = Me.RecordsetClone
frmClone.AddNew
frmClone.SomeField = Me.SomeTextbox
frmClone.AnotherField = Me.AnotherTextbox
'etc.
frmClone.Update
frmClone.Bookmark = frmClone.LastModified
lngMainID = frmClone!ID

'**********************************
'Add the middle table's records.
'**********************************
Set db = dbEngine(0)(0)
Set rsTarget = db.OpenRecordset("MyTargetTable", dbOpenDynaset,
dbAppendOnly)
strSql = "SELECT * FROM MyMiddleTable WHERE MyForeignkey = " & Me.ID & ";"
Set rsSource = db.OpenRecordset(strSql)

Do while not rsSource.EOF
rsTarget.AddNew
rsTarget!MyForeignKey = lngMainID
rsTarget!Field1 = rsSource!SomeField
rsTarget!AnotherField = rsSource!FieldWhatever
'etc.
rsTarget.Update
rsTarget.Bookmark = rsTarget.LastModified
lngMiddleID = rsTarget!ID

'**********************************
'Add the inner table's related records.
'**********************************
strSql = "INSERT INTO ...
db.Execute strSql, dbFailOnError
rsSource.MoveNext
Loop
rsSource.Close
rsTarget.Close

'**********************************
'Clean up, and show the new record.
'**********************************
Me.Bookmark = frmClone.Bookmark
set rsTarget = Nothing
set rsSource = Nothing
Set rsClone = Nothing
set db = Nothing

 

[Scott Parmelee]

Allen,
Thanks so much for responding. That made it much clearer - I was able to
get it working with no problem.
Thanks again,
Scott