Link to home
Start Free TrialLog in
Avatar of jdc1944
jdc1944Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Copying one Record into a new Record

I have a small Access database that i use in a shop for creating orders which are placed over the Christmas period.

Every time a customer places an order, i obviously manually enter this into the database.  However, because this is for Christmas food orders, i very often get "Can i have the same as last year?"  What this means is that i have to use one of my pre-made queries to search for all the customers previous orders and then write down the food order they placed the previous year, then create a new order and re-enter this info.  What would save a huge amount of time is if i was able to select the order made in the previous year and just (preferably using a command button with some VB behind it) paste it into a new record, creating a new order.  Is this possible at all.

I have several tables in the database.  One is for customer details, one is for a list of products and another stores details on the order, such as customer ID, Product ID, order date etc.

Whats my best way of achieving this?

Thanks.
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Place a button on your form.

When the command button wizard pops up, select "Record Operations".

You'll see a 'Duplicate Record' option...
Avatar of jdc1944

ASKER

as easy as that?  I'll give that a go, thanks.
Here's a method that allows you to modify the cloned record on the fly or leave out fields that should not be copied - like the order date:

https://www.experts-exchange.com/questions/26621508/Clone-Record.html?anchorAnswerId=34158936#a34158936

/gustav
Avatar of jdc1944

ASKER

Thanks for that.  The problem that i am having is that the form contains a sub form (the order), when i use that code on the main form, it creates a new order thing including the customer details but does not contain any of the order from the sub form.

Would have to add that code to the subform as well so that when the copy button is pressed on the main form it invokes the same code on the subform if that makes sense?
Yes, that makes sense.
You copy the main record, read the Id from the new record as it must be used for the copy of the subrecords, then copy the subrecords applying this new Id as the foreign key to the copy of the subrecords.

/gustav
Jdc1944,

Just as an aside, if you have issues implementing solutions, let us know by responding with details.  The sooner the better ... that helps get your questions resolved more quickly since we are monitoring the questions in case you need additional help or alternate suggestions.
Avatar of jdc1944

ASKER

sorry for the delay in responding, I've been away.

So far I got the two clones working separately - in that there is a button on the main form which duplicates the customer details and then clicking on the button on the subform copies the order details.  Now i just need to link the two together.  I might be completely wrong on this but my plan was to change the onclick event of the subforms button to public and then call it from the main form using:

 Me.frmEdit_Order_Subform.Command17_Click

Open in new window


However doing so produces the following error:

Method or data member not found.

Can I actually call the subforms onclick event from the main form or have I just tried to call it wrongly?
I guess it should read:

Me!frmEdit_Order_Subform.Form.Command17_Click

/gustav
Avatar of jdc1944

ASKER

Thanks for that, that works great.

One problem i have noticed with the code from the link you sent me is that it only copies the first record in the subform.  How would i go about getting it to loop through and copy each record?
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jdc1944

ASKER

Thanks for that.  I've got the above working on its own i.e it just copies the subform however i'm having a bit of trouble integrating it with the main form.  One issue i get is an error message that says:

"No Current Record"

Also whereabouts would i invoke the subform code from the main form to enable me to get the new Order_ID that is generated.  Wherever i seem to put it i can only get the old Order_ID
Don't know about the no current record but you can pick up the new Id here:

      ' Go to the new record and sync form.
      .MoveLast

/gustav
Avatar of jdc1944

ASKER

Thats what I thought but when it does that the sub form is then empty as it is reflecting the new order
Well, you will have to wait to sync the main form until the new childrecords are created:

        .MoveLast
        ' Copy childrecords.
        Me.Bookmark = .Bookmark

/gustav
Avatar of jdc1944

ASKER

thanks for that.

the no current record one is bizzare.  The first time I try to copy an order it works fine, try again and i get that error message.  The only way for it to work again is close the form and then open it back up again.  It errors on this:

Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If

Open in new window


Where the =.value has no current record.

Does this help you solve it at all.

I appreciate all your help so far.
Avatar of jdc1944

ASKER

just sorted it, i added
.MoveFirst

Open in new window


To make sure it was always starting at the first record.


Thanks for all you help gustav,  I really appreciate it