[AccessD] Copy a record in a table

Rocky,

    Both the sub-routines work directly upon the table in question. There is no dependence upon any form or its recordset. You have only to supply the name of table, name of primary key field and PK value of target record (desired to be copied).

    As suggested earlier, the procedure as per (B) should be preferred as it is far more efficient. It does not require opening of the table. Even a recordset is not required to be opened.

    Note - Sub-routine (A), though not the optimum alternative, had been given merely to demonstrate a direct rough & ready approach as  demanded by you.

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

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


  A.D.:

  Thanks for those routines.  A looks like it will work only if the recordset is bound to the form? And then will copy the current record? Or will it work on any table even if it is not the record source?  

  I'm not familiar with all those commands but I'll look them up in Help.

  Thanks again fro your response.

  Best,

  Rocky


  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
  Sent: Tuesday, November 14, 2006 11:32 PM
  To: Access Developers discussion and problem solving
  Subject: Re: [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