duplicating records in forms and sub forms

[New TO Access]

eI have a form that uses the duplicate rcord button to create a new record
and form so that the user can edit the record/form and create a new instance
of the record. I do this to retain as much of the orignal data as possible
and to prevent having to re-key all the data. Only some fields require
editing.

I also have a sub-form attached which calls data from a different table.

How do I create a duplicate of both forms/records at the same time? If I
don't, the sub form on the new record doesn't contain the data relative to
the first record.

 

[Allen Browne]

The example below shows how to duplicate the record in the form and its
related records in the subform. It uses DAO to duplicate the main form
record so you can get the new primary key value, and then executes an Append
query statement to duplicate the related records for the subform. It then
moves to the new record so you can view/modify the newly created record.

The example is for an invoice in the main form, and the invoice line items
in the subform. Change the field names and subform name to match your actual
names. If you have difficulty forming the Append query statement, mock one
up and switch it to SQL View to get an example.

All versions of Access have a reference to the DAO library except 2000 and
2002. If you get an "unknown type" error in these versions, see:
http://members.iinet.net.au/~allenbrowne/ser-38.html

-------------------------start code------------------------
Private Sub cmdDupe_Click()
Dim sSQL As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

'Duplicate the main record
With Me.RecordsetClone
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
'etc
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

'Duplicate the related records.
If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then
sSQL = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount ) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail.Item, " & _
"tInvoiceDetail.Amount FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail.InvoiceID = " & Me.InvoiceID &
");"
db.Execute sSQL, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

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

Set db = Nothing
End Sub
-------------------------end code------------------------