Need to duplicate records in a subtable

[Charles]

I have 2 tables joined together in a one-to-many relationship. Together
they represent transaction detail in a sales environment. On a form
attached to the tables, using the Command Button Wizard (Duplicate Record),
I created a command button that creates a new record by duplicating the
record the user is currently viewing. The problem is that I can't get the
data in the subtable to duplicate. Right now, only the data from the main
table gets duplicated and the user has to manually duplicate the transaction
detail of the subtable. Does anyone know how to get the subtable records to
be duplicated as well?





Below is the code created by the wizard in Access 2000:



Private Sub cmdDuplicateRecord_Click()

On Error GoTo Err_cmdDuplicateRecord_Click





DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70

DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70

DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append



Exit_cmdDuplicateRecord_Click:

Exit Sub



Err_cmdDuplicateRecord_Click:

MsgBox Err.Description

Resume Exit_cmdDuplicateRecord_Click



End Sub



Also, is this code the best way to duplicate a record?

 

[Charles]

Check that, a recent post by Dirk Goldgar solves the problem with

RunCommand acCmdSelectRecord
RunCommand acCmdCopy
RunCommand acCmdPasteAppend

sorry about that

 

[Charles]

As soon as I change the primary key, the records duplicated in the subtable
disappear. Any ideas?

It appears that an actual copy of the subtable records are not being made
with the following code:

RunCommand acCmdSelectRecord
RunCommand acCmdCopy
RunCommand acCmdPasteAppend

 

[John Vinson]

As soon as I change the primary key, the records duplicated in the subtable
disappear. Any ideas?

It appears that an actual copy of the subtable records are not being made
with the following code:

RunCommand acCmdSelectRecord
RunCommand acCmdCopy
RunCommand acCmdPasteAppend

Charles,

There are several hundred posts every day to this newsgroup. You're
not the only one asking questions, and it can be rather difficult for
those of us who answer many questions every day to instantly recall
every thread.

Please either continue the original thread, or copy over at least
enough context that we can get some idea of the problem and the prior
replies. I'd prefer not to need to go search in Google to find that
information.

 

[Charles]

I am really sorry about that Mr. Vinson and will try to use better
discretion next time. Just got a little ahead of myself today.

 

[Charles]

Does this mean your not going to answer my question?

 

[Bas Cost Budde]

I have 2 tables joined together in a one-to-many relationship. Together
they represent transaction detail in a sales environment. On a form
attached to the tables, using the Command Button Wizard (Duplicate Record),
I created a command button that creates a new record by duplicating the
record the user is currently viewing. The problem is that I can't get the
data in the subtable to duplicate. Right now, only the data from the main
table gets duplicated and the user has to manually duplicate the transaction
detail of the subtable. Does anyone know how to get the subtable records to
be duplicated as well?

What is the primary key of the transaction table (on the one-side, I
presume)? If it is an autonumber, you can create a new transaction
record with a 'simple' INSERT INTO statement, retrieve the new key, and
use another INSERT INTO for the details records. Well, that is the
procedure anyway. In other cases you have to get a new key first.

Personally, I don't like the term 'duplicate' because that's what we're
trying to avoid using keys. Aren't we? But I understand the direction of
thought here. I wrote my own accounting application that does just this
to create periodical entries.

 

[John Vinson]

Does this mean your not going to answer my question?

Oh, I'll try to answer the question if I can understand it! Please
repost *with the context* so I have a clear idea what the question
actually is.

 

[Charles]

Alright, I know this must be frustrating for you MVP's to wade through some
of the muck we throw at ya, but I'll try to be more thoughtful and concise
this time. Like I said earlier, I just got a little ahead of myself
yesterday and I'm sorry bout that.

Anyway, let me make another stab at it so here it is: I have a main table
attached to a subtable. In a form attached to these tables I have a
"button" that creates a new record by copying the current record being
viewed. The problem is, all the fields from the Main table are copied but
the fields from the subtable are not. The way it sits now, the user must
reenter all the data in the subtable. Which really isn't a huge problem and
life will go on, but I would like to know why it is, when I perform this
copy, the data from BOTH tables aren't copied? And how do I get it to
include a copy of the data in the subtable as well?

PS. I used the Command Button Wizard (Duplicate Record) to build the button
and have not added any other code to it.

Again, thanks for your help.

 

[John Vinson]

Alright, I know this must be frustrating for you MVP's to wade through some
of the muck we throw at ya, but I'll try to be more thoughtful and concise
this time. Like I said earlier, I just got a little ahead of myself
yesterday and I'm sorry bout that.

Anyway, let me make another stab at it so here it is: I have a main table
attached to a subtable. In a form attached to these tables I have a
"button" that creates a new record by copying the current record being
viewed. The problem is, all the fields from the Main table are copied but
the fields from the subtable are not. The way it sits now, the user must
reenter all the data in the subtable. Which really isn't a huge problem and
life will go on, but I would like to know why it is, when I perform this
copy, the data from BOTH tables aren't copied? And how do I get it to
include a copy of the data in the subtable as well?

PS. I used the Command Button Wizard (Duplicate Record) to build the button
and have not added any other code to it.

The command button wizard does just what it's advertised to do: copies
a record on the current form.

The Subform IS ANOTHER FORM. Its recordsource is related to the
current record, but it's a different form and a different table.

You'll need to run an Append query to append the records related to
this mainform record, appending from the related table into itself,
with the appropriate modifications (I don't know what those might be).

Generally, I'd be leery of creating lots of redundant records in hopes
that a user will modify them... all too often they don't get fixed.