Duplicating Record problem

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

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

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

Select all Open in new window

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

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

The code above will do that.

(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,

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

Select all Open in new window

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.

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

Select all 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

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

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)

Thanks to you Gustav, Dale and Pat for your replies