Command Button Duplicate for forms and subforms

  • Thread starter Thread starter Shao
  • Start date Start date
S

Shao

The Command Button Duplicate works great if it just one
form. However, my form has four subforms. When I click
the Duplicate Button only the parent form duplicates and
the subforms don't copy over to the next record.

The only way I can think of is the write some major VB and
experiment. Is there an easier way to connect the
subforms and parent forms to duplicate?

Thanks for your help. I appreciate it.
 
Yes, it does involve some code to duplicate the main record and any related
records.

Use DAO to duplicate the main record, so you can get the new primary key
value. You need this value in the foreign key of the related records. The
related records can be created with an Append query statement.

This example shows how to duplicate the invoice currently displayed in a
form, along with its line items:

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
.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.", vbInformation, "Information"
End If

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

Set db = Nothing
End Sub
 
Back
Top