Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

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.
0
jdc1944
Asked:
jdc1944
  • 8
  • 6
  • 2
1 Solution
 
mbizupCommented:
Place a button on your form.

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

You'll see a 'Duplicate Record' option...
0
 
jdc1944Author Commented:
as easy as that?  I'll give that a go, thanks.
0
 
Gustav BrockCIOCommented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jdc1944Author 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?
0
 
Gustav BrockCIOCommented:
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
0
 
mbizupCommented:
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.
0
 
jdc1944Author 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?
0
 
Gustav BrockCIOCommented:
I guess it should read:

Me!frmEdit_Order_Subform.Form.Command17_Click

/gustav
0
 
jdc1944Author 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?
0
 
Gustav BrockCIOCommented:
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
0
 
jdc1944Author 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
0
 
Gustav BrockCIOCommented:
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
0
 
jdc1944Author Commented:
Thats what I thought but when it does that the sub form is then empty as it is reflecting the new order
0
 
Gustav BrockCIOCommented:
Well, you will have to wait to sync the main form until the new childrecords are created:

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

/gustav
0
 
jdc1944Author 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.
0
 
jdc1944Author 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
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 8
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now