Duplicate record in Form and Subform

  • Thread starter Thread starter Mik
  • Start date Start date
M

Mik

Hello,

I have created a form with a button, and wish to copy/duplicate the
current record displayed on the form and associated subform.
Reason... there are many fields within my forms / tables, and
sometimes, two records may be very similar with only minor
differences. The duplication function would simplify the input.
I have searched the web for posts regarding this matter (read Allen
Browne's post etc..) and have struggled to understand how / what i am
to do.
I am a newbie to access, and vba is very limited.

So, I have a form (Form1) which populates a Table (Table1), and the
form contains a subform (Subform1) which populates a table (Table2).
The link between Table1 & Table2 is a field called "Equip_No".
I currently have NO primary key.

I know this post is brief... if you need more info to assist, let me
know.
My code (extracted from other posts) is below.
What should I change?

'************************************************
Private Sub cmdduplicate_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 "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!Equip_No = Me.Equip_No
'etc for other fields in Table1.
.Update

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

'Duplicate the related records: append query.
If Me.[Subform1].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Table2] ( Equip_No, etc1, etc2,
etc3) " & _
"SELECT " & lngID & " As NewID, Equip_No, etc1,
etc2, etc3" & _
"FROM [Table2] WHERE Equip_No = " & Me.Equip_No &
";"
DBEngine(0)(0).Execute strSql, dbFailonerror
Else
MsgBox "Main record duplicated, but there were no
related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If
Equip_No.SetFocus
Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdduplicate_Click"
Resume Exit_Handler

End Sub
'****************************************

Thanks in advance,
Mik
 
Back
Top