Duplicate Record Without The Key Field

[PC User]

I found this function to duplicate the current record; however, the
copy includes the key field. Does anyone have some ideas to use this
without copying the key field?

Public Function DupeRecord()

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

End Function

Thanks,

PC

 

[Allen Browne]

To duplicate only some of the fields from the current record into a new
record, AddNew to the RecordsetClone.

Here's an example:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

(Just ignore the part about duplicating the related record in the subform.)

 

[PC User]

Thanks very much Allen. I thought maybe someone else may have come
accross this problem before and solved it. I have quite a few fields
though and was hoping for a more generic function. Sorry to be
pickie. I did find a generic function for the main form and maybe I
can use the concept from your suggestion to adapt it to include the
subform. This is the code that I found.
=====================================================
Function fCopyRecord(strTable As String, varPKVal) As Long
'Leigh Purvis, Database Developer
'Newcastle or South Yorkshire
' Utter Access Forum
'Copies a record in a specified table
'Accepts table name and Primary key value.
'Currently assumes a simplistic single PK field

'The fairly generic function below will add a copy of a record to the
same table
'based on an existing PK value and return the PK value of the new
record.
'You could then use similar code to create the child records.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field
Dim strPKName As String
Dim strFields As String

Set db = CurrentDb
Set tdf = db(strTable)

For Each idx In tdf.Indexes
If idx.Primary Then
strPKName = idx.Fields(0).Name
Exit For
End If
Next

For Each fld In tdf.Fields
If fld.Name <> strPKName Then
strFields = strFields & ",[" & fld.Name & "]"
End If
Next
strFields = Mid(strFields, 2)

Set qdf = db.CreateQueryDef("")

qdf.SQL = "INSERT INTO [" & strTable & "] (" & strFields & ") " &
_
"SELECT " & strFields & " FROM [" & strTable & "] " & _
"WHERE [" & strPKName & "]= " & varPKVal
qdf.Execute
If qdf.RecordsAffected > 0 Then
With db.OpenRecordset("SELECT @@Identity")
fCopyRecord = .Fields(0)
.Close
End With
End If

Set db = Nothing
Set qdf = Nothing
Set tdf = Nothing
Set idx = Nothing
Set fld = Nothing

End Function
=====================================================
I appreciate your reply.

PC