Need to copy parent-child-child records (re: Allen Browne's metho

[Dr Steevil]

Allen,

I posted this at the end of another thread, but figured since that thread
was over 2 years old I'd start a new post.

I am needing to duplicate records from a parent form and its underlying
child subforms.

I've gone through your method you posted and after a few tries at it, got it
to work for me beautifully. Thanks very much for this! Now on to my problem.

Basically, I need to go one subform more. I have a second subform (which of
course links to the subform above i.e. a parent-child-child relationship. As
I said, the first two I can duplicate using your method, but the "grandchild"
is where I have problems.

Some info: Here are the 3 tables
tblBids
idsBidID (primary)
miscellaneous other fields (i'll leave these out here for simplicity sake)

tblAssemblies
idsAssembliesID (primary)
idsBidID (foreign key in my subform from tblBids)
miscellaneous other fields

tblAssemblyComponents
idsAssemblyComponentsID (primary)
idsAssembliesID (foreign key in sub-subform from tblAssemblies)
miscellaneous other fields

Here's my code to duplicate the parent and first child sub forms (skipping
down to the part that duplicates tblAssemblies):

If Me.[tblAssemblies_subform].Form.RecordsetClone.RecordCount > 0 Then

strSqlAssemblies = "INSERT INTO [tblAssemblies] ( idsBidID,
chrFixtureType, chrFixtureCode ) " & _
"SELECT " & lngID & " As NewID,
chrFixtureType, chrFixtureCode " & _
"FROM [tblAssemblies] WHERE idsBidID = " &
Me.idsBidID & ";"

DBEngine(0)(0).Execute strSqlAssemblies, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

Again, that works great, just need to know how to go about duplicating the
records for tblAssemblyComponents_subform.

By the way, the whole purpose of this is to enable me to build a bid then
build another bid by copying another similar one and just making some changes
to it.

Thanks for your help. I've bookmarked your site too and have gained quite a
few valuable insights from it. I love trying to figure this stuff out and
don't usually ask for help (preferring to find it on my own then work through
it to make it work for me).

 

[Allen Browne]

I think we are talking about this article:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

It illustrates 2 ways to append data:
- the main form uses AddNew on the RecordsetClone of the form, so it can get
the ID of the newly added record.
- the subform uses an Append query to add the data, using the new ID value
as the foreign key.

Your question involves another level in the middle. Like the top-level, you
need the ID value to create the middle-level records, and then you need the
ID of *each* one of those to create the lowest level records.

My suggestion would be use the recordset approach (AddNew and Update), in a
loop, to create the middle-level records. For each one in the loop, you can
then exeucte the append query to create the lower level ones as well.

This will involve a bit more programming to do the extra loop, but the basic
principle is still the same as the AddNew.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I posted this at the end of another thread, but figured since that thread
was over 2 years old I'd start a new post.

I am needing to duplicate records from a parent form and its underlying
child subforms.

I've gone through your method you posted and after a few tries at it, got
it
to work for me beautifully. Thanks very much for this! Now on to my
problem.

Basically, I need to go one subform more. I have a second subform (which
of
course links to the subform above i.e. a parent-child-child relationship.
As
I said, the first two I can duplicate using your method, but the
"grandchild"
is where I have problems.

Some info: Here are the 3 tables
tblBids
idsBidID (primary)
miscellaneous other fields (i'll leave these out here for simplicity
sake)

tblAssemblies
idsAssembliesID (primary)
idsBidID (foreign key in my subform from tblBids)
miscellaneous other fields

tblAssemblyComponents
idsAssemblyComponentsID (primary)
idsAssembliesID (foreign key in sub-subform from tblAssemblies)
miscellaneous other fields

Here's my code to duplicate the parent and first child sub forms (skipping
down to the part that duplicates tblAssemblies):

If Me.[tblAssemblies_subform].Form.RecordsetClone.RecordCount > 0 Then

strSqlAssemblies = "INSERT INTO [tblAssemblies] ( idsBidID,
chrFixtureType, chrFixtureCode ) " & _
"SELECT " & lngID & " As NewID,
chrFixtureType, chrFixtureCode " & _
"FROM [tblAssemblies] WHERE idsBidID = " &
Me.idsBidID & ";"

DBEngine(0)(0).Execute strSqlAssemblies, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

Again, that works great, just need to know how to go about duplicating the
records for tblAssemblyComponents_subform.

By the way, the whole purpose of this is to enable me to build a bid then
build another bid by copying another similar one and just making some
changes
to it.

Thanks for your help. I've bookmarked your site too and have gained quite
a
few valuable insights from it. I love trying to figure this stuff out and
don't usually ask for help (preferring to find it on my own then work
through
it to make it work for me).

 

[Dr Steevil]

Yes sir, you are correct. My problem is figuring out how to do the loop. I
love learning, so I'm not asking for verbatem code, but examples of how do do
a loop. I sort of figured thats the way I needed to go, but wanted to get
some input first.

Thanks!

 

[Allen Browne]

Here's a basic example of how to open a records and loop through the
records:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Yes sir, you are correct. My problem is figuring out how to do the loop.
I
love learning, so I'm not asking for verbatem code, but examples of how do
do
a loop. I sort of figured thats the way I needed to go, but wanted to get
some input first.

Thanks!

Allen,

I posted this at the end of another thread, but figured since that thread
was over 2 years old I'd start a new post.

I am needing to duplicate records from a parent form and its underlying
child subforms.

I've gone through your method you posted and after a few tries at it, got
it
to work for me beautifully. Thanks very much for this! Now on to my
problem.

Basically, I need to go one subform more. I have a second subform (which
of
course links to the subform above i.e. a parent-child-child relationship.
As
I said, the first two I can duplicate using your method, but the
"grandchild"
is where I have problems.

Some info: Here are the 3 tables
tblBids
idsBidID (primary)
miscellaneous other fields (i'll leave these out here for simplicity
sake)

tblAssemblies
idsAssembliesID (primary)
idsBidID (foreign key in my subform from tblBids)
miscellaneous other fields

tblAssemblyComponents
idsAssemblyComponentsID (primary)
idsAssembliesID (foreign key in sub-subform from tblAssemblies)
miscellaneous other fields

Here's my code to duplicate the parent and first child sub forms
(skipping
down to the part that duplicates tblAssemblies):

If Me.[tblAssemblies_subform].Form.RecordsetClone.RecordCount > 0 Then

strSqlAssemblies = "INSERT INTO [tblAssemblies] ( idsBidID,
chrFixtureType, chrFixtureCode ) " & _
"SELECT " & lngID & " As NewID,
chrFixtureType, chrFixtureCode " & _
"FROM [tblAssemblies] WHERE idsBidID = " &
Me.idsBidID & ";"

DBEngine(0)(0).Execute strSqlAssemblies, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

Again, that works great, just need to know how to go about duplicating
the
records for tblAssemblyComponents_subform.

By the way, the whole purpose of this is to enable me to build a bid then
build another bid by copying another similar one and just making some
changes
to it.

Thanks for your help. I've bookmarked your site too and have gained
quite a
few valuable insights from it. I love trying to figure this stuff out
and
don't usually ask for help (preferring to find it on my own then work
through
it to make it work for me).

 

[Dr Steevil]

Thanks a million. I got it working. Took all day but finally with your help
and a couple of others along the way, I got it! Probably learned more today
than any other single day since I've been doing this.

Here's a basic example of how to open a records and loop through the
records:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Yes sir, you are correct. My problem is figuring out how to do the loop.
I
love learning, so I'm not asking for verbatem code, but examples of how do
do
a loop. I sort of figured thats the way I needed to go, but wanted to get
some input first.

Thanks!

Allen,

I posted this at the end of another thread, but figured since that thread
was over 2 years old I'd start a new post.

I am needing to duplicate records from a parent form and its underlying
child subforms.

I've gone through your method you posted and after a few tries at it, got
it
to work for me beautifully. Thanks very much for this! Now on to my
problem.

Basically, I need to go one subform more. I have a second subform (which
of
course links to the subform above i.e. a parent-child-child relationship.
As
I said, the first two I can duplicate using your method, but the
"grandchild"
is where I have problems.

Some info: Here are the 3 tables
tblBids
idsBidID (primary)
miscellaneous other fields (i'll leave these out here for simplicity
sake)

tblAssemblies
idsAssembliesID (primary)
idsBidID (foreign key in my subform from tblBids)
miscellaneous other fields

tblAssemblyComponents
idsAssemblyComponentsID (primary)
idsAssembliesID (foreign key in sub-subform from tblAssemblies)
miscellaneous other fields

Here's my code to duplicate the parent and first child sub forms
(skipping
down to the part that duplicates tblAssemblies):

If Me.[tblAssemblies_subform].Form.RecordsetClone.RecordCount > 0 Then

strSqlAssemblies = "INSERT INTO [tblAssemblies] ( idsBidID,
chrFixtureType, chrFixtureCode ) " & _
"SELECT " & lngID & " As NewID,
chrFixtureType, chrFixtureCode " & _
"FROM [tblAssemblies] WHERE idsBidID = " &
Me.idsBidID & ";"

DBEngine(0)(0).Execute strSqlAssemblies, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

Again, that works great, just need to know how to go about duplicating
the
records for tblAssemblyComponents_subform.

By the way, the whole purpose of this is to enable me to build a bid then
build another bid by copying another similar one and just making some
changes
to it.

Thanks for your help. I've bookmarked your site too and have gained
quite a
few valuable insights from it. I love trying to figure this stuff out
and
don't usually ask for help (preferring to find it on my own then work
through
it to make it work for me).

 

[Allen Browne]

Thanks a million. I got it working. ... Probably learned more
today than any other single day since I've been doing this.

Excellent! That's what these groups are all about.