Copying a record with subrecords to a new record

[Joe Williams]

I have a main form with a subfroim that displays related records from a
normalized table. Is there a way to copy a record to a new record if the
original record has subrecords attached to it?

Example- I have a sales order with sales order detail lines in a seperate
table. I would like to have the use click a button and have it copy the
header record as well as the assocaited detail records to a new
record/subrecord.

The built in button to copy records only takes the header record, it does
not copy any subrecords that have been entered

Thanks for you help in advance

- joe

 

[Allen Browne]

This example shows how to duplicate the main form record using DAO, the
related records using an Append query statement, and then make the new
record the current one.

The main form contains an invoice, and the subform the line items for the
invoice:

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 for other fields.
.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

 

[Guest]

Hi Allen

Could you change your code from DAO to ADO please? It will be very helpful.

Thanks.
Nat Mongkol

 

[Allen Browne]

Yes, you could use ADO code, if the RecordSource for your form is ADO. I'm
not going to do it for you though. You can work through the example from
there.

 

[Guest]

Here is my try on ADO code. Hopefully it will be useful.

Private Sub cmdDupe_Click()
Dim sSQL As String
Dim lngInvID As Long
Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command


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

'Duplicate the main record
Set rst = Me.RecordsetClone

With rst
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
'etc for other fields.
.Update
.Move 0, adBookmarkLast
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 &
");"
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = sSQL
.CommandType = adCmdText
.Execute
End With
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

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

rst.close
Set rst = Nothing
Set cmd = Nothing

End Sub