I would like to "duplicate" a parent record and its child records.

[Steve]

i have a couple of tables with a "one-to-many" relationship.

i want enable my users to essentially "duplicate" the data from a parent
record into a new record, but also duplicate "child" records associated with
that parent, linking the duplicated child records to the newly duplicated
"parent" record.

ideally, i would like to do this as the result of a command button "on
click" event.

however, i really have run out of ideas! can somebody help???

thanks in advance! Steve

 

[Allen Browne]

See:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

 

[Steve]

apologies... i meant "many-to-many" (with the primary key of each table
linked through a junction table).

still appreciate the help though please!!

 

[Allen Browne]

The exmaple posted will still work for you junction table (assuming the
relevant value already exists in the other lookup table.)

 

[Steve]

hi allen,

thanks for this- i think its "almost" there... when i run the code, i keep
getting a "Runtime Error 3061: Too Few Parameters expected 1" issue.

as part of my junction table, i have setup a field "jnc_JunctionID" which is
an autonumber field. i have deliberately left this out of the SQL statement
on the basis that, whatever fields are omitted from the SQL, the default will
be applied for the new record(s). even when i include that field as part of
the statement, i still get the same error.

any ideas??

 

[Allen Browne]

The request for a parameter means there is a name in the SQL statement that
JET cannot resolve. It might be a misspelled field name, or a reference to
something like [Forms].[Form1].[Text0].

It's fine to leave an AutoNumber field with no value assigned to it. (I'm
cautious with assuming Access will assign the default value for other
fields, though, as I'm not sure this has been consistent across all
versions, service packs, and append methods.)

 

[Steve]

i've checked my table field names and all ok. here is the output of "strSQL"
from the "local" window when the fault occurs and I go to debug....

"INSERT INTO [tbl_ProposalAssetJunction] (jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition) SELECT 46 As jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition FROM [tbl_ProposalAssetJunction] WHERE ProposalID = 12;"

any ideas?

The request for a parameter means there is a name in the SQL statement that
JET cannot resolve. It might be a misspelled field name, or a reference to
something like [Forms].[Form1].[Text0].

It's fine to leave an AutoNumber field with no value assigned to it. (I'm
cautious with assuming Access will assign the default value for other
fields, though, as I'm not sure this has been consistent across all
versions, service packs, and append methods.)

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

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

hi allen,

thanks for this- i think its "almost" there... when i run the code, i keep
getting a "Runtime Error 3061: Too Few Parameters expected 1" issue.

as part of my junction table, i have setup a field "jnc_JunctionID" which
is
an autonumber field. i have deliberately left this out of the SQL
statement
on the basis that, whatever fields are omitted from the SQL, the default
will
be applied for the new record(s). even when i include that field as part
of
the statement, i still get the same error.

any ideas??

 

[Allen Browne]

Looks okay, assuming the data types match.

Create a new query.
Cancel the Add Table dialog.
Switch to SQL View (View menu.)
Paste the statement it.
Run.
What name parameter does Access ask for?
Does that give any clues?

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

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

i've checked my table field names and all ok. here is the output of
"strSQL"
from the "local" window when the fault occurs and I go to debug....

"INSERT INTO [tbl_ProposalAssetJunction] (jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition) SELECT 46 As jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition FROM [tbl_ProposalAssetJunction] WHERE ProposalID = 12;"

any ideas?

The request for a parameter means there is a name in the SQL statement
that
JET cannot resolve. It might be a misspelled field name, or a reference
to
something like [Forms].[Form1].[Text0].

It's fine to leave an AutoNumber field with no value assigned to it. (I'm
cautious with assuming Access will assign the default value for other
fields, though, as I'm not sure this has been consistent across all
versions, service packs, and append methods.)

hi allen,

thanks for this- i think its "almost" there... when i run the code, i
keep
getting a "Runtime Error 3061: Too Few Parameters expected 1" issue.

as part of my junction table, i have setup a field "jnc_JunctionID"
which
is
an autonumber field. i have deliberately left this out of the SQL
statement
on the basis that, whatever fields are omitted from the SQL, the
default
will
be applied for the new record(s). even when i include that field as
part
of
the statement, i still get the same error.

any ideas??

:

See:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

i have a couple of tables with a "one-to-many" relationship.

i want enable my users to essentially "duplicate" the data from a
parent
record into a new record, but also duplicate "child" records
associated
with
that parent, linking the duplicated child records to the newly
duplicated
"parent" record.

ideally, i would like to do this as the result of a command button
"on
click" event.

however, i really have run out of ideas! can somebody help???

 

[Steve]

mmm... did as you suggested.. it asks for the [ProposalID] parameter. i
provided "12" as the input - it then asked me if i wanted to append all 46
rows. there are only actually 6 rows associated with paramter 12... not sure
why its asking me to confirm "append 46"... i have checked the data types and
they are all long integer.

Looks okay, assuming the data types match.

Create a new query.
Cancel the Add Table dialog.
Switch to SQL View (View menu.)
Paste the statement it.
Run.
What name parameter does Access ask for?
Does that give any clues?

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

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

i've checked my table field names and all ok. here is the output of
"strSQL"
from the "local" window when the fault occurs and I go to debug....

"INSERT INTO [tbl_ProposalAssetJunction] (jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition) SELECT 46 As jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition FROM [tbl_ProposalAssetJunction] WHERE ProposalID = 12;"

any ideas?

The request for a parameter means there is a name in the SQL statement
that
JET cannot resolve. It might be a misspelled field name, or a reference
to
something like [Forms].[Form1].[Text0].

It's fine to leave an AutoNumber field with no value assigned to it. (I'm
cautious with assuming Access will assign the default value for other
fields, though, as I'm not sure this has been consistent across all
versions, service packs, and append methods.)

hi allen,

thanks for this- i think its "almost" there... when i run the code, i
keep
getting a "Runtime Error 3061: Too Few Parameters expected 1" issue.

as part of my junction table, i have setup a field "jnc_JunctionID"
which
is
an autonumber field. i have deliberately left this out of the SQL
statement
on the basis that, whatever fields are omitted from the SQL, the
default
will
be applied for the new record(s). even when i include that field as
part
of
the statement, i still get the same error.

any ideas??

:

See:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

i have a couple of tables with a "one-to-many" relationship.

i want enable my users to essentially "duplicate" the data from a
parent
record into a new record, but also duplicate "child" records
associated
with
that parent, linking the duplicated child records to the newly
duplicated
"parent" record.

ideally, i would like to do this as the result of a command button
"on
click" event.

however, i really have run out of ideas! can somebody help???

 

[Allen Browne]

Sounds like Access is identifying that name with something else.
Try aliasing the field with another name, e.g.:
12 AS MyLiteral

When Access gets the names of fields or tables muddled, Name AutoCorrect is
the number one suspect:
http://allenbrowne.com/bug-03.html

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

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

mmm... did as you suggested.. it asks for the [ProposalID] parameter. i
provided "12" as the input - it then asked me if i wanted to append all 46
rows. there are only actually 6 rows associated with paramter 12... not
sure
why its asking me to confirm "append 46"... i have checked the data types
and
they are all long integer.

Looks okay, assuming the data types match.

Create a new query.
Cancel the Add Table dialog.
Switch to SQL View (View menu.)
Paste the statement it.
Run.
What name parameter does Access ask for?
Does that give any clues?

i've checked my table field names and all ok. here is the output of
"strSQL"
from the "local" window when the fault occurs and I go to debug....

"INSERT INTO [tbl_ProposalAssetJunction] (jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition) SELECT 46 As jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition FROM [tbl_ProposalAssetJunction] WHERE ProposalID =
12;"

any ideas?

:

The request for a parameter means there is a name in the SQL statement
that
JET cannot resolve. It might be a misspelled field name, or a
reference
to
something like [Forms].[Form1].[Text0].

It's fine to leave an AutoNumber field with no value assigned to it.
(I'm
cautious with assuming Access will assign the default value for other
fields, though, as I'm not sure this has been consistent across all
versions, service packs, and append methods.)

hi allen,

thanks for this- i think its "almost" there... when i run the code,
i
keep
getting a "Runtime Error 3061: Too Few Parameters expected 1" issue.

as part of my junction table, i have setup a field "jnc_JunctionID"
which
is
an autonumber field. i have deliberately left this out of the SQL
statement
on the basis that, whatever fields are omitted from the SQL, the
default
will
be applied for the new record(s). even when i include that field as
part
of
the statement, i still get the same error.

any ideas??

:

See:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

i have a couple of tables with a "one-to-many" relationship.

i want enable my users to essentially "duplicate" the data from a
parent
record into a new record, but also duplicate "child" records
associated
with
that parent, linking the duplicated child records to the newly
duplicated
"parent" record.

ideally, i would like to do this as the result of a command
button
"on
click" event.

however, i really have run out of ideas! can somebody help???

 

[Steve]

thanks for your continued support! i tried your suggestion below, and ran the
SQL in the query window by changing into query "datasheet" view. the query
datasheet view returned 46 rows, each one with my "new" proposalID - so that
bit works...

however, there are only actually 6 child records in the junction table
associated with the proposal number i am copying, not 46 (which is every row
in the junction table)

also, it seems if i leave the junction table "key" field out of the SQL,
then it fails with the "expeted 1" message.

any thoughts??

Sounds like Access is identifying that name with something else.
Try aliasing the field with another name, e.g.:
12 AS MyLiteral

When Access gets the names of fields or tables muddled, Name AutoCorrect is
the number one suspect:
http://allenbrowne.com/bug-03.html

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

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

mmm... did as you suggested.. it asks for the [ProposalID] parameter. i
provided "12" as the input - it then asked me if i wanted to append all 46
rows. there are only actually 6 rows associated with paramter 12... not
sure
why its asking me to confirm "append 46"... i have checked the data types
and
they are all long integer.

Looks okay, assuming the data types match.

Create a new query.
Cancel the Add Table dialog.
Switch to SQL View (View menu.)
Paste the statement it.
Run.
What name parameter does Access ask for?
Does that give any clues?

i've checked my table field names and all ok. here is the output of
"strSQL"
from the "local" window when the fault occurs and I go to debug....

"INSERT INTO [tbl_ProposalAssetJunction] (jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition) SELECT 46 As jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition FROM [tbl_ProposalAssetJunction] WHERE ProposalID =
12;"

any ideas?

:

The request for a parameter means there is a name in the SQL statement
that
JET cannot resolve. It might be a misspelled field name, or a
reference
to
something like [Forms].[Form1].[Text0].

It's fine to leave an AutoNumber field with no value assigned to it.
(I'm
cautious with assuming Access will assign the default value for other
fields, though, as I'm not sure this has been consistent across all
versions, service packs, and append methods.)

hi allen,

thanks for this- i think its "almost" there... when i run the code,
i
keep
getting a "Runtime Error 3061: Too Few Parameters expected 1" issue.

as part of my junction table, i have setup a field "jnc_JunctionID"
which
is
an autonumber field. i have deliberately left this out of the SQL
statement
on the basis that, whatever fields are omitted from the SQL, the
default
will
be applied for the new record(s). even when i include that field as
part
of
the statement, i still get the same error.

any ideas??

:

See:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

i have a couple of tables with a "one-to-many" relationship.

i want enable my users to essentially "duplicate" the data from a
parent
record into a new record, but also duplicate "child" records
associated
with
that parent, linking the duplicated child records to the newly
duplicated
"parent" record.

ideally, i would like to do this as the result of a command
button
"on
click" event.

however, i really have run out of ideas! can somebody help???

 

[Steve]

solved it !

allen- thanks for your help on this one... "you the man!" as they say!

regards, steve

thanks for your continued support! i tried your suggestion below, and ran the
SQL in the query window by changing into query "datasheet" view. the query
datasheet view returned 46 rows, each one with my "new" proposalID - so that
bit works...

however, there are only actually 6 child records in the junction table
associated with the proposal number i am copying, not 46 (which is every row
in the junction table)

also, it seems if i leave the junction table "key" field out of the SQL,
then it fails with the "expeted 1" message.

any thoughts??

Sounds like Access is identifying that name with something else.
Try aliasing the field with another name, e.g.:
12 AS MyLiteral

When Access gets the names of fields or tables muddled, Name AutoCorrect is
the number one suspect:
http://allenbrowne.com/bug-03.html

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

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

mmm... did as you suggested.. it asks for the [ProposalID] parameter. i
provided "12" as the input - it then asked me if i wanted to append all 46
rows. there are only actually 6 rows associated with paramter 12... not
sure
why its asking me to confirm "append 46"... i have checked the data types
and
they are all long integer.

:

Looks okay, assuming the data types match.

Create a new query.
Cancel the Add Table dialog.
Switch to SQL View (View menu.)
Paste the statement it.
Run.
What name parameter does Access ask for?
Does that give any clues?

i've checked my table field names and all ok. here is the output of
"strSQL"
from the "local" window when the fault occurs and I go to debug....

"INSERT INTO [tbl_ProposalAssetJunction] (jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition) SELECT 46 As jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition FROM [tbl_ProposalAssetJunction] WHERE ProposalID =
12;"

any ideas?

:

The request for a parameter means there is a name in the SQL statement
that
JET cannot resolve. It might be a misspelled field name, or a
reference
to
something like [Forms].[Form1].[Text0].

It's fine to leave an AutoNumber field with no value assigned to it.
(I'm
cautious with assuming Access will assign the default value for other
fields, though, as I'm not sure this has been consistent across all
versions, service packs, and append methods.)

hi allen,

thanks for this- i think its "almost" there... when i run the code,
i
keep
getting a "Runtime Error 3061: Too Few Parameters expected 1" issue.

as part of my junction table, i have setup a field "jnc_JunctionID"
which
is
an autonumber field. i have deliberately left this out of the SQL
statement
on the basis that, whatever fields are omitted from the SQL, the
default
will
be applied for the new record(s). even when i include that field as
part
of
the statement, i still get the same error.

any ideas??

:

See:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

i have a couple of tables with a "one-to-many" relationship.

i want enable my users to essentially "duplicate" the data from a
parent
record into a new record, but also duplicate "child" records
associated
with
that parent, linking the duplicated child records to the newly
duplicated
"parent" record.

ideally, i would like to do this as the result of a command
button
"on
click" event.

however, i really have run out of ideas! can somebody help???

 

[Allen Browne]

Good. You've pinned down what the parameter was referring to, and solved
that bit.

The next step will be to identify why the query is returning so many
records. I don't see an obvious reason: there's only one table, and one
criterion. Presumably your criterion is correct, so I'm not sure what to
suggest. I presume you did carry through on the Name AutoCorrect issue.

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

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

thanks for your continued support! i tried your suggestion below, and ran
the
SQL in the query window by changing into query "datasheet" view. the query
datasheet view returned 46 rows, each one with my "new" proposalID - so
that
bit works...

however, there are only actually 6 child records in the junction table
associated with the proposal number i am copying, not 46 (which is every
row
in the junction table)

also, it seems if i leave the junction table "key" field out of the SQL,
then it fails with the "expeted 1" message.

any thoughts??

Sounds like Access is identifying that name with something else.
Try aliasing the field with another name, e.g.:
12 AS MyLiteral

When Access gets the names of fields or tables muddled, Name AutoCorrect
is
the number one suspect:
http://allenbrowne.com/bug-03.html

mmm... did as you suggested.. it asks for the [ProposalID] parameter. i
provided "12" as the input - it then asked me if i wanted to append all
46
rows. there are only actually 6 rows associated with paramter 12... not
sure
why its asking me to confirm "append 46"... i have checked the data
types
and
they are all long integer.

:

Looks okay, assuming the data types match.

Create a new query.
Cancel the Add Table dialog.
Switch to SQL View (View menu.)
Paste the statement it.
Run.
What name parameter does Access ask for?
Does that give any clues?

i've checked my table field names and all ok. here is the output of
"strSQL"
from the "local" window when the fault occurs and I go to debug....

"INSERT INTO [tbl_ProposalAssetJunction] (jnc_ProposalID,
jnc_AssetID,
jnc_AssetPosition) SELECT 46 As jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition FROM [tbl_ProposalAssetJunction] WHERE ProposalID
=
12;"

any ideas?

:

The request for a parameter means there is a name in the SQL
statement
that
JET cannot resolve. It might be a misspelled field name, or a
reference
to
something like [Forms].[Form1].[Text0].

It's fine to leave an AutoNumber field with no value assigned to
it.
(I'm
cautious with assuming Access will assign the default value for
other
fields, though, as I'm not sure this has been consistent across all
versions, service packs, and append methods.)

hi allen,

thanks for this- i think its "almost" there... when i run the
code,
i
keep
getting a "Runtime Error 3061: Too Few Parameters expected 1"
issue.

as part of my junction table, i have setup a field
"jnc_JunctionID"
which
is
an autonumber field. i have deliberately left this out of the SQL
statement
on the basis that, whatever fields are omitted from the SQL, the
default
will
be applied for the new record(s). even when i include that field
as
part
of
the statement, i still get the same error.

any ideas??

:

See:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

i have a couple of tables with a "one-to-many" relationship.

i want enable my users to essentially "duplicate" the data
from a
parent
record into a new record, but also duplicate "child" records
associated
with
that parent, linking the duplicated child records to the newly
duplicated
"parent" record.

ideally, i would like to do this as the result of a command
button
"on
click" event.

however, i really have run out of ideas! can somebody help???

 

[Steve]

yes, i checked the autocorrect issue. the issue was as you suggested,
duplicate parameter naming! doh!

once again, thanks for all your help!

steve

Good. You've pinned down what the parameter was referring to, and solved
that bit.

The next step will be to identify why the query is returning so many
records. I don't see an obvious reason: there's only one table, and one
criterion. Presumably your criterion is correct, so I'm not sure what to
suggest. I presume you did carry through on the Name AutoCorrect issue.

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

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

thanks for your continued support! i tried your suggestion below, and ran
the
SQL in the query window by changing into query "datasheet" view. the query
datasheet view returned 46 rows, each one with my "new" proposalID - so
that
bit works...

however, there are only actually 6 child records in the junction table
associated with the proposal number i am copying, not 46 (which is every
row
in the junction table)

also, it seems if i leave the junction table "key" field out of the SQL,
then it fails with the "expeted 1" message.

any thoughts??

Sounds like Access is identifying that name with something else.
Try aliasing the field with another name, e.g.:
12 AS MyLiteral

When Access gets the names of fields or tables muddled, Name AutoCorrect
is
the number one suspect:
http://allenbrowne.com/bug-03.html

mmm... did as you suggested.. it asks for the [ProposalID] parameter. i
provided "12" as the input - it then asked me if i wanted to append all
46
rows. there are only actually 6 rows associated with paramter 12... not
sure
why its asking me to confirm "append 46"... i have checked the data
types
and
they are all long integer.

:

Looks okay, assuming the data types match.

Create a new query.
Cancel the Add Table dialog.
Switch to SQL View (View menu.)
Paste the statement it.
Run.
What name parameter does Access ask for?
Does that give any clues?

i've checked my table field names and all ok. here is the output of
"strSQL"
from the "local" window when the fault occurs and I go to debug....

"INSERT INTO [tbl_ProposalAssetJunction] (jnc_ProposalID,
jnc_AssetID,
jnc_AssetPosition) SELECT 46 As jnc_ProposalID, jnc_AssetID,
jnc_AssetPosition FROM [tbl_ProposalAssetJunction] WHERE ProposalID
=
12;"

any ideas?

:

The request for a parameter means there is a name in the SQL
statement
that
JET cannot resolve. It might be a misspelled field name, or a
reference
to
something like [Forms].[Form1].[Text0].

It's fine to leave an AutoNumber field with no value assigned to
it.
(I'm
cautious with assuming Access will assign the default value for
other
fields, though, as I'm not sure this has been consistent across all
versions, service packs, and append methods.)

hi allen,

thanks for this- i think its "almost" there... when i run the
code,
i
keep
getting a "Runtime Error 3061: Too Few Parameters expected 1"
issue.

as part of my junction table, i have setup a field
"jnc_JunctionID"
which
is
an autonumber field. i have deliberately left this out of the SQL
statement
on the basis that, whatever fields are omitted from the SQL, the
default
will
be applied for the new record(s). even when i include that field
as
part
of
the statement, i still get the same error.

any ideas??

:

See:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

i have a couple of tables with a "one-to-many" relationship.

i want enable my users to essentially "duplicate" the data
from a
parent
record into a new record, but also duplicate "child" records
associated
with
that parent, linking the duplicated child records to the newly
duplicated
"parent" record.

ideally, i would like to do this as the result of a command
button
"on
click" event.

however, i really have run out of ideas! can somebody help???

 

[Allen Browne]

Okay. Great news.

 

[Dr Steevil]

Allen,

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).