Copying record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello there:

I do have a problem. I have a form with a few subforms, and some of those
subforms do also contain subforms. The point is that I need to be able of
making copy of the main record, and all the information contained in the
subforms and its subforms. As first I would like to know if is actually
possible doing so, and also, in case it is possible, to get a clue about how
this could be done.

Thanks.
 
Yes, it is possible to programmatically duplicate the main form record, and
also the related records in the subforms, and their related records.

The example below gives you the first step. It is designed to go in the
Click event of a "duplicate" button on the main form. It adds a new record
to the RecordsetClone of the main form (an invoice in this example), and
gets the new primary key number. Then it executes an Append query statement
to add the same rows to the new record in the InvoiceDetail table. Finally,
it makes the new record current in the main form, which loads the subforms
as well.

In your case, you will also need to know the primary key value for the new
record in the subform so you can also use this in the Append query statement
for your sub-subform. If you are using Access 2000 or later, you can query
database for the @@IDENTITY of the last append. Example shown in the second
function below.


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
End If

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

Set db = Nothing
End Sub


Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function
 
Back
Top