Duplicating Record problem

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
Ronald MalkAsked:
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.

Gustav BrockCIOCommented:
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
Ronald MalkAuthor 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
Gustav BrockCIOCommented:
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:


Open in new window

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.

Ronald MalkAuthor 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
Gustav BrockCIOCommented:
Excel? I thought you would copy a record in the form?

The code above will do that.
Ronald MalkAuthor 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,
Gustav BrockCIOCommented:
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
                    ' Copy field.
                    .Value = rst.Fields(.Name).Value
                End If
            End With

Open in new window

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, Developing Solutions LLCCommented:
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

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.

Ronald MalkAuthor 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
Gustav BrockCIOCommented:
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.

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

Open in new window

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
Ronald MalkAuthor 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
            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)
                         'Copy field.
                        .Value = rst.Fields(.Name).Value
                    End If
                End With
        End With
     'Store location of new record.
    Bookmark = rstAdd.Bookmark


     '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)
Ronald MalkAuthor Commented:
Thanks to you Gustav, Dale and Pat for your replies
Gustav BrockCIOCommented:
Great! Thanks for the feedback.
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.