The best way would be to copy the existing record into a new record and then
edit the copy. I wonder why you need to do this. It indicates to me that you
***might*** have design problems in your table structure if you need to
duplicate most (or many) of the fields in one record in the next record.
Here is an old posting from Dirk Goldgar that I have in my archives. I've not
used it, but Dirk Goldgar is an MVP and I believe his coding skills are superior.
Duplicate record except for primary key, non-updatable fields, and specified
list of fields
From: "Dirk Goldgar" <
[email protected]>
'Needs to check to see if form's addnew property is true and if a current
record exists.
'Needs to make sure that the recordsetclone bookmark matches the form's
recordset bookmark
'----- start of code -----
Function CloneCurrentRecord(frm As Access.Form, ParamArray NotField())
Dim fld As DAO.Field
Dim strFieldName As String
Dim blnCopyField As Boolean
Dim intI As Integer
' Save the current record, if it's dirty
If frm.Dirty Then
On Error GoTo Err_SavingRecord
frm.Dirty = False
End If
' If we're at a new record, then there's nothing to copy.
If frm.NewRecord Then
MsgBox _
"Unable to copy blank record.", _
vbInformation, _
"Not Copied"
Exit Function
End If
On Error GoTo Err_General
' Create a new record in the form's recordsetclone, and
' copy all the updatable fields from the current record.
With frm.RecordsetClone
.AddNew
For Each fld In frm.Recordset.Fields
strFieldName = fld.Name
' Should this field be copied?
blnCopyField = True
If (fld.Attributes And dbAutoIncrField) <> 0 Then
blnCopyField = False
ElseIf (fld.Attributes And dbUpdatableField) = 0 Then
blnCopyField = False
ElseIf UBound(NotField) >= LBound(NotField) Then
For intI = LBound(NotField) To UBound(NotField)
If strFieldName = NotField(intI) Then
blnCopyField = False
Exit For
End If
Next intI
End If
If blnCopyField Then
.Fields(strFieldName).Value = fld.Value
End If
Next fld
.Update
.Bookmark = .LastModified
frm.Bookmark = .Bookmark
End With
Exit_Point:
Exit Function
Err_SavingRecord:
MsgBox _
"Error: Unable to save the current record " & _
"in order to copy it." & _
vbCr & vbCr & _
"Error number = " & Err.Number & _
", description = '" & Err.Description & "'", _
vbExclamation, _
"Can't Save Record"
Resume Exit_Point
Err_General:
MsgBox _
"Error: Unexpected error occurred while copying record." & _
vbCr & vbCr & _
"Error number = " & Err.Number & _
", description = '" & Err.Description & "'", _
vbExclamation, _
"Error Copying Record"
Resume Exit_Point
End Function
'----- end of code -----
The function is designed to copy all updatable fields from the current record
to a new record, and then move to that record. It uses the form's Recordset
and RecordsetClone, and assumes these are DAO recordsets, so it wouldn't work
in an ADP (though it could probably be modified to do so). Autonumber fields
and nonupdatable fields are excluded automatically, but it also provides for
an optional list of fields not to be copied.
You could call it using a line of code in a form's module, like this:
CloneCurrentRecord Me
or
CloneCurrentRecord Me, "ExcludeThisField", "ThisOneToo"
Or you could call it from a function expression in the OnClick event property
of a command button, like this:
=CloneCurrentRecord([Form])
As I said, I've only barely tested this, so there could be bugs or unknown
twists to it. In particular, I haven't tested the "nonupdatable" check for
fields.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi there
I would like to create a button for a form that would allow me to edit a
record, make changes, and save it as a new record.
I don't want to save a record and have it overwrite earlier information.
How will I create this button?
Thank you in advance
.
