duplicate data from one record to a new record

Hope I can explain this....   Here goes...

I have a form with a subform for the details.  The package id in the top of the main form changes but sometimes the details are the same for different packages.  Does anyone know of a way to create a button or some way to take the current record that is open and create a brand new record but the package number would be different or for that matter any of the info in the main form?  I've attached a screen shot of the form.
lrollinsIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can use the INSERT INTO command, like this:
INSERT INTO MyTable (Field1, Field2 Field3)
       (SELECT Field1, 'New Field2 Value', Field3
          FROM MyTable
         WHERE some_clause_that_selects_your_single_record);

Open in new window

lrollinsIT ManagerAuthor Commented:
I meant to add this note as well....

The main form is table1 and the subform is table2 and they are linked by the list_id.  so it may not be that simple when using 2 tables that are linked.
Gustav BrockCIOCommented:
You will first copy the parent record:


then, having the new key, the childrecords. Read on in that thread.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

lrollinsIT ManagerAuthor Commented:
I'll try this hopefully over the weekend.  I looks exactly like I need.  Thanks
lrollinsIT ManagerAuthor Commented:
I tried to do this but it gets too confusing for me.  I'm not that great with Access..   just enough to be dangerous.  I guess I'm having trouble knowing what to change in the code to reference my form and subform.
Gustav BrockCIOCommented:
The subform's recordset is here:

Set rstSource = Me!frmEdit_Order_Subform.Form.RecordsetClone

Note that frmEdit_Order_Subform is not the form but the name of the subform control.

lrollinsIT ManagerAuthor Commented:
Well I'm not getting any error messages but nothing is happening either when I click the button.  I would think it would open a new window with the cloned info but it's not.
Gustav BrockCIOCommented:
No, the usual method is that the current record is copied (with or without childrecords) and the copy becomes the current record, so the user can continue editing.

Of course, the actual behaviour is defined by the developer.

lrollinsIT ManagerAuthor Commented:
Well it's not doing anything.  I did find this on Allen Browne's website which is similar to yours...  


I just keep playing with it.  Hopefully I get it figured out.
Gustav BrockCIOCommented:
Well, it is the simplest and fastest method. But without code it is hard to advise further.

lrollinsIT ManagerAuthor Commented:
I know and I realize there are many factors to this.  I may just forget about it for now because I just don't know enough about it.  However, I will give you an A grade because I'm sure it would work if I knew more.

Gustav BrockCIOCommented:
If you posted your test, we probably could find out.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.