Duplicate Record

  • Thread starter Thread starter Zanstemic
  • Start date Start date
Z

Zanstemic

I have a data entry form when completely filled out, I would like to
duplicate the record.

I have one field that increments and should not be duplicated with the new
record. Any suggestions on how to best approach duplicating all the fields
except for one?

Thanks in advance for the help
 
There is a command button wizard you could probably use to do this. If it's
an AutoNumber, it will fail to duplicate that one field anyway. If there are
other problem fields (unbound, bound to expressions, ...) this simple
approach probably won't work.

For a more robust and configurable approach, see:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html
Just ignore the block dealing with the subform records.
 
This was extremely helpful.

The error I'm receiving is "The changes you requested to the table were not
successful becasue they would create duplicate values in the index, primary
key, or relationship. Change the data in the field or fileds that contain
duplicate data, remove the index, or redefine the index to permit duplicate
entries and try again."

Below is the code altered from the web instructions: The three fields that
are part of .AddNew can have duplicates. One of the tables does have a
required number that is incremented when the form is opened as a default
value =Format(Date(),"yymmdd") & Format([txtRegistration Case Number],"000").

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.


'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Please Enter Information to Duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
![Claim Number] = Me.cboClaimNumber
!SpecialtyID = Me.cboSpecialty
![Date Received] = Now()
'etc for other fields.
.Update


'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = ![Claim Number]


'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub

I'm guessing it has something to do with the lngID but need some guidance.
 
Allen,
I want to thank you for all the help and guidance. I tried a different
method to create a new form first that is working well. It allowed me to
create a new record first which advances the Auto Number and than places the
information to duplicate.

Here is the updated code:

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim ctrl1 As String 'string example
Dim ctrl2 As String 'string example


'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Please Enter Information to Duplicate."
Else
'Duplicate the main record: add to new form.

ctrl1 = cboClaimNumber
ctrl2 = cboSpecialty

DoCmd.GoToRecord , , acNewRec
[Claim Number] = ctrl1
SpecialtyID = ctrl2

End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
 
Back
Top