[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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.430 / Virus Database: 268.14.5/534 - Release Date: 11/14/2006
3:58 PM