Copying one Record into a new Record

jdc1944
jdc1944 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Place a button on your form.

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

You'll see a 'Duplicate Record' option...

Author

Commented:
as easy as that?  I'll give that a go, thanks.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26621508.html#a34158936

/gustav
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
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.

Author

Commented:
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?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I guess it should read:

Me!frmEdit_Order_Subform.Form.Command17_Click

/gustav

Author

Commented:
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?
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You could modify this function:
Public Sub CopyRecords(ByVal lngNewFK As Long)
  
  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  Dim lngLoop     As Long
  Dim lngCount    As Long

  Set rstSource = Me!frmEdit_Order_Subform.Form.RecordsetClone
  Set rstInsert = rstSource.Clone
  With rstSource
    lngCount = .RecordCount
    For lngLoop = 1 To lngCount
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = "FK" Then
                ' Insert default new foreign key from copy of new master record.
                rstInsert.Fields(.Name).Value = lngNewFK
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
      End With
      .MoveNext
    Next
    rstInsert.Close
    .Close
  End With
  
  Set rstInsert = Nothing
  Set rstSource = Nothing
  
End Sub

Open in new window

Air code, not tested.

/gustav

Author

Commented:
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
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

Commented:
Thats what I thought but when it does that the sub form is then empty as it is reflecting the new order
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, you will have to wait to sync the main form until the new childrecords are created:

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

/gustav

Author

Commented:
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.

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial