Avatar of Ronald Malk
Ronald MalkFlag for Australia

asked on 

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
Microsoft Access

Avatar of undefined
Last Comment
Gustav Brock
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

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
Avatar of Ronald Malk
Ronald Malk
Flag of Australia image

ASKER

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
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

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

Avatar of Ronald Malk
Ronald Malk
Flag of Australia image

ASKER

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
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

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

The code above will do that.
Avatar of Ronald Malk
Ronald Malk
Flag of Australia image

ASKER

(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,
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

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

Avatar of PatHartman
PatHartman
Flag of United States of America image

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.
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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
Avatar of Ronald Malk
Ronald Malk
Flag of Australia image

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Ronald Malk
Ronald Malk
Flag of Australia image

ASKER

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)
Avatar of Ronald Malk
Ronald Malk
Flag of Australia image

ASKER

Thanks to you Gustav, Dale and Pat for your replies
SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo