Duplicate Record with SubForms

[Guest]

Using Access2000,

I have a form with two subforms attached. I would like to create a button
on the main form that would duplicate the entire record displayed including
the information in the two subforms. I was able to use a wizard to create a
button that would create a new record with duplicate info in the fields on
the main form, but not the subforms. When I looked at the Event Procedure
the wizard had created, it contained the following:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

Not sure what this means. Anyone have any ideas how I can duplicate a
record showing on a form with all the attendant information in the subforms?

 

[John Nurick]

Hi Dennis,

Creating new records for the subforms will be a lot more complicated,
and I don't think that the wizard code gives a good starting point.
Assuming that there's a 1:M relationship between the main table and the
tables to which the subforms are bound, it may be necessary to create
zero, one, or more records in these tables.

What I'd do is write code to

1) build and execute the SQL statement for an append query that creates
a new record in the main table, using data from the current record.

2) from the new record, get the values of the linking field(s) for the
subforms.

3) for each of the subform tables, build and execute an SQL statement
that creates the necessary records (selecting all the records that match
the value of the linking field in the current record on the main form,
and appending corresponding records that have the linking field set to
the new value (from step 2) and all the other fields unchanged.

But before doing this I'd think hard about the data structure. If you're
creating bunches of duplicate records on the "many" side of a 1:M
relationship, I suspect the 1:M is really a M:M.

 

[Guest]

Hi John,
I'm working on a similar scenario: I need to duplicate a record (parent form
based on table Jobs) link to the subform (based on table Samples) by "JobID".
Relation is 1=M.
Many times we need to duplicate the job with the samples and modify some
information (date, labid, etc).

Do you have or can provide an example of SQL statement to accomplish this?
When we need to duplicate only the job, a copy record was fine, but
duplicating the record AND the many samples linked to it by the JobID is
getting more complicated.

Thanks in advance. Looking forward to lear something new today!