[AccessD] Copy a record in a table

Rocky,

    Apparently, you are looking for a straightforward method to copy an existing record to new record in the same table, without having to  bother with individual field names. It is presumed that the table has autonumber type primary key.

    Use of an insert statement with wild card for field names looks tempting and deceptively simple. However, it gets ruled out as the action would attempt to create a duplicate value in primary key field.

    A rough & ready method involves copying of desired record to clipboard and then pasting it on to the new record. This works smoothly, without attracting any error on a/c of attempted duplication of primary key value. Instead, correct autoonumber gets automatically  assigned to the newly pasted record, over-ruling the PK value in clipboard. Sample sub-routine named P_CopyRecordToNew(), as given at (A) below, demonstrates this approach.

    For ready reference, sample sub-routine P_InsertRecordCopy(), as given at (B) below, represents a more formal approach for accomplishing the same task.

    Once you put these subs in a general module, invoking the one at (B) should be equally convenient as the one at (A). Eventually, you might prefer the one at (B).

Best wishes,
A.D.Tejpal
---------------

A) Copy existing record to new record in same table
     (Rough & Ready method)
==================================
Sub P_CopyRecordToNew(ByVal TableName _
                    As String, ByVal PkName As String, _
                    ByVal PkNum As Long)
    DoCmd.OpenTable TableName
    DoCmd.GoToControl PkName
    DoCmd.FindRecord PkNum
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.GoToRecord , , acNewRec
    DoCmd.RunCommand acCmdPaste
    DoCmd.Close acTable, TableName, acSaveYes
End Sub
==================================

B) Copy existing record to new record in same table
     (Formal method)
==================================
Sub P_InsertRecordCopy(ByVal TableName _
                    As String, ByVal PkName As String, _
                    ByVal PkNum As Long)
    Dim Qst As String, FieldList As String
    Dim fd As Field
    Dim db As DAO.Database
    
    Set db = DBEngine(0)(0)
    FieldList = ""
    For Each fd In db.TableDefs(TableName).Fields
        If fd.Name <> PkName Then
            FieldList = FieldList & _
                            IIf(Len(FieldList) > 0, _
                            ", ", "") & fd.Name
        End If
    Next
    
    Qst = "Insert Into " & TableName & " (" & _
            FieldList & ") Select " & FieldList & _
            " From " & TableName & " Where " & _
            PkName & " = " & PkNum & ";"
    db.Execute Qst, dbFailOnError
    
    Set fd = Nothing
    Set db = Nothing
End Sub
==================================

  ----- Original Message ----- 
  From: Beach Access Software 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, November 15, 2006 06:41
  Subject: [AccessD] Copy a record in a table

  Dear List: 

  Is there a quick or slick or elegant way to copy a record in a table to a
  new record in the same table - just duplicating a record 

  I've done it using DAO and cycling through the fields and creating the new record and adding it but that seems awfully crude.  

  MTIA 

  Rocky