Duplicating Record problem

Ronald Malk
Ronald Malk used Ask the Experts™
on
Hi every one, I need to create a new record via button on the form will copy details and paste it for the new record, I’m using the code below, It doesn't paste anything,  it’s doing the copying but it’s copying the form controls names as well, I pasted the content of the clipboard in excel sheet and there was 2 rows the top row having the names of form controls instead of the fields names and the bottom Row have the copied record well but not in order,

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste

Also I used the command Button Wizard still doing same thing
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It is usually much faster to use DAO to duplicate a record, and it requires no update of the form.
An example is here:

How do I write VBA code to duplicate a record

Author

Commented:
Thank you Gustav for the reply,
Yes I pasted the code of the link but still giving me exactly same result as I mentioned above
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
What does not in order mean?

If the pasted record is expected to be position with the current records, you must requery the form after the copying:

Me.Requery

Open in new window

JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Author

Commented:
What I meant is when I pasted the clipboard content into the Excel sheet It showed me 2 Rows the top one has all the names of the controls on the Form.
the Second Row has the form entered details records but not in the same sequence as it is in the table, anyway I think if I fix the problem and prevent it from copying the controls names of the form then that more likely would solve the problem
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Excel? I thought you would copy a record in the form?

The code above will do that.

Author

Commented:
(It's MS Access Form),  
Yep should do it, but for some reason it's pasting nothing in the new record, because what's pasting is conflicting with the table so it gets rejected, anyway It's late Midnight  now I'll leave it till tomorrow I will try it on a different form,  MS Access,
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That's what the middle section is for - to adjust or skip some fields for various reasons:

            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                ElseIf .Name = "Qtr" Then
                    ' Pick user selected value from unbound textbox named, say, NewQtr.
                    .Value = Nz(Me!NewQtr.Value, 0)
                ElseIf .Name = "SomeOtherField" Then
                    ' Insert other value.
                    .Value = SomeOtherValue
                Else
                    ' Copy field.
                    .Value = rst.Fields(.Name).Value
                End If
            End With

Open in new window

Distinguished Expert 2017

Commented:
Ronald,
When you copy the form and paste it into Excel, what you are pasting is what is on the FORM not what is in a table.  So, I would expect to see the fields pasted based on the tab order of the form.  Keep in mind that the tab order might be different from the visible order.  So, if the form shows fld1, fld2, fld3 but they paste as fld2, fld1, fld3, then look at the tab order and you will see that that is the reason.  You can reset the tab order globally by pressing the tab order icon or you can do it for individual controls by changing the tab index value.

Once you move into a database world, copy and paste are not the preferred method of copying data.  You can use the RecordsetClone to insert new rows or you can use an append query.  Just keep in mind that it makes no sense to insert duplicate records so you need to consider that in your code logic and make sure you don't duplicate the primary key or a unique index.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Also, if you are trying to copy a record in Access, I prefer to use a SQL Statement, something like (assumes that Field1 is the ID - autonumber) field:
lngID = me.txt_ID
strSQL = "INSERT INTO yourTable (Field2, Field3, Field4,..., FieldN) " _
       & "SELECT Field2, Field3, Field4,...FieldN " _
       & "FROM yourTable WHERE ID = " & me.txt_ID
currentdb.execute strsql, dbfailonerror
me.requery

Open in new window

Then, you could add some additional code to go to determine the ID value associated with that new record, and set the focus to the copy.

HTH
Dale

Author

Commented:
Thanks for both reply, actually I was trying to get the code from Dale because it would give me the option of select what I need,
But I couldn't get it working ,
Please Dale or Someone tells me what is wrong with it.

Dim lngID As Long
Dim strSQL As String
lngID = Me.TxOrdID
strSQL = "INSERT INTO Tbl_Stock_Ordrs (Field2, Field3, Field4) " _
       & "SELECT Field2, Field3, Field4" _
       & "FROM yourTable WHERE OrdID = " & Me.TxOrdID
CurrentDb.Execute strSQL, dbFailOnError   '<<Showing error here
Me.Requery
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You miss a space here:

 & "SELECT Field2, Field3, Field4 " _

Open in new window

That said, using SQL behind the scene will not solve your problem inserting duplicate values:

what's pasting is conflicting with the table so it gets rejected,

So, first try manually to duplicate a record, identifying the offending field(s).
Then adjust the code here to skip or modify the values of those fields to allow for an insert:

            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                ElseIf .Name = "FirstOffendingField" Then
                    ' Insert non-offending value.
                    .Value = SomeValue
                ElseIf .Name = "NextOffendingField" Then
                    ' Insert other non-offending value.
                    .Value = SomeOtherValue

                ' etc.

                Else
                    ' Copy field.
                    .Value = rst.Fields(.Name).Value
                End If
            End With

Open in new window

Author

Commented:
Finally Go it working, Thank you Gustav,
Here is my code
     Dim rst         As DAO.Recordset
    Dim rstAdd      As DAO.Recordset
    Dim fld         As DAO.Field

    Dim Count       As Integer
    Dim Item        As Integer
    Dim Bookmark    As Variant
    Dim NewId       As Long

     'Copy record.
    Set rstAdd = Me.RecordsetClone
    Set rst = rstAdd.Clone

     'Move to current record.
    rst.Bookmark = Me.Bookmark
        With rstAdd
            .AddNew
            For Each fld In .Fields
                With fld
                    If .Attributes And dbAutoIncrField Then
                         'Skip Autonumber or GUID field.
                    ElseIf .Name = "OrdNm" Then
                    ElseIf .Name = "DtPaid" Then
                    ElseIf .Name = "Paid" Then
                   
                         'Pick user selected value from unbound textbox named, say, NewQtr.
                    ' .Value = Nz(Me!NewQtr.Value, 0)
                    Else
                         'Copy field.
                        .Value = rst.Fields(.Name).Value
                    End If
                End With
            Next
            .Update
        End With
     'Store location of new record.
    Bookmark = rstAdd.Bookmark

    rst.Close
    rstAdd.Close

     'Move to the new recordcopy.
    Me.Bookmark = Bookmark

    Set fld = Nothing
    Set rstAdd = Nothing
    Set rst = Nothing
    DoCmd.RunCommand acCmdRecordsGoToLast
    Me.TxInvNm.Value = Nz(DMax("OrdNm", "[Tbl_Stock_Ordrs]")) + 1

But I had to disable this line: .Value = Nz(Me!NewQtr.Value, 0)

Author

Commented:
Thanks to you Gustav, Dale and Pat for your replies
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Great! Thanks for the feedback.

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