[AccessD] Copy a record in a table

Hi Rocky

Crude? How come? DAO is normally the fastest method to add a record to a recordset.
Here's a basic function:

<code>

Public Function CopyRecord( _
  ByVal strTable As String, _
  ByVal strId As String, _
  ByVal lngId As Long) _
  As Boolean

  Dim dbs     As DAO.Database
  Dim rst     As DAO.Recordset
  Dim rstAdd  As DAO.Recordset
  Dim fld     As DAO.Field
  Dim strFld  As String
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Select * From " & strTable & " Where " & strId & "=" & lngId & ";")
  Set rstAdd = dbs.OpenRecordset("Select Top 1 * From " & strTable & ";")
  
  With rstAdd
    .AddNew
      For Each fld In rstAdd.Fields
        With fld
          strFld = .Name
          If Not strFld = strId Then
            .Value = rst.Fields(strFld).Value
          End If
        End With
      Next
    .Update
    .Close
  End With
  rst.Close
  
  Set fld = Nothing
  Set rstAdd = Nothing
  Set rst = Nothing
  Set dbs = Nothing
  
End Function

</code>

Of course, you will need to refine this for compound indices, other unique indices, OLE fields, default values etc. Also add error handling.

/gustav

>>> bchacc at san.rr.com 15-11-2006 02:11 >>>

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