how do i duplicate a record from a form and subform?

H

hams45

I have a form using a table and two subforms on this form using two different
tables. The subforms are using continuous forms. The data is all linked. I
have created a duplicate record button on my form. I can only get the data to
replicate on the main form. Is there any way to get my data on my subforms to
duplicate as well while creating the same link?
 
A

Allen Browne

The example below duplicates the invoice in the main form, and the invoice
line items in the subform.

It works like this:
- Saves any changes to the main form.
- Checks there is a record in the main form to duplicate.
- Copies the fields from the form to a new record in the RecorsetClone of
the main form.
- Gets the InvoiceID of the new record, since we need to use that for the
related records.
- Executes an Append query statement to duplicate the related records also.
- Displays the new record (by setting the form's bookmark to the new record
in the RecordsetClone.)


Private Sub cmdDupe_Click()
Dim strSql 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 for other fields.
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

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

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

Set db = Nothing
End Sub
 
Top