copy form and subform data to new record

[AkAlan via AccessMonster.com]

I have a main form with 2 subforms and my customer wants to be able to make a
copy of most fields from the main and subforms so he doesn't have to reenter
manually. There are a lot of fields so I don't want to have to make a
variable for each entry and use a recordset to update the tables. I have made
an update query and it works for the main form data but not the subforms. The
main forms primary key is an autonumber so jet taqkes care of making the new
record unique. The problem with the subforms is that their primary keys are
not autonumber, they are derived from the main forms primary key so when I
try to copy the record I get a duplicate value error which makes sense.

I have a couple of ideas on what to try next but was hoping someone might
have had to do this already and have a solution in place. thanks.

 

[Allen Browne]

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

The most flexible approach is to list each of the fields and ensure you are
assiging the desired values (e.g. changing the date of the new duplicate to
today).

If you prefer, it is possible to loop through the Fields of the Recordset,
and - unless they need special handling (a SELECT CASE for the special
cases, with an ESLE for the rest) - concatenate their name into the source
and target parts of the SQL string (the INSERT and SELECT clauses.)