You need to have an Append Query run from the code behind your command button. Select Make a query in Design You neeed to have an Append Query run from the code behind your command button. After setting up your new table for the records to be added to:
1) Select "Make a query in Design View" and select the table/query to base it on (the one your original form was based on will do)
2) Select the various fields you want to append to the new table.
3) Drop down the Query Menu and select Append Query
4) Access will ask you for the name of the table to append the data to
5) On the Query Grid go to a field that is unique to the record (say the Part Number, maybe) and under on the Criteria line for this field enter:
This tells Access to append the current record to the new table. Save the Query. Go into Design View for your form and select the command button. Place this code behind your button:
'This turns off warning about appending record to new table
'This adds the copy of the record to the new table
DoCmd.OpenQuery "YourAppendQueryName" 'Adds record to new table
DoCmd.SetWarnings True 'Turns warnings back on! Remember to do this!
I think this covers everything, but I don't have a copy of Access at hand, so I may have forgotten a step. If you have any problems come on back! Someone will help!
Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007