Copy Record.

  • Thread starter Thread starter .Troy
  • Start date Start date
T

.Troy

I have a database that has two one-to-many tables (table
A 1-2-many to table B, table B 1-2-many to table C)

I'd like to copy a record in table A to a new record and
be able to have table B and C copy as well.

Can anyone help with achieving this? I am having trouble
with the B & C tables.

thanks for any advice!

Troy.
 
You want to duplicate the main record, and the related records in 2 tables.

You need to know the p.key of the new main record before you can create the
related records for it. Use DAO to duplicate the main record (because it
exposes the key value), and then you can execute append query statements to
duplicate the related records as well.

This example assumes you have a form open, and the user is looking at the
main record to duplicate. (It's an invoice with detail records in this
example):

Private Sub cmdDupe_Click()
Dim sSQL As String
Dim db As DAO.Database
Dim lngID As Long 'The new primary key value.

Set db = DBEngine(0)(0)

If Me.Dirty Then 'Save first
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
lngID = !InvoiceID

'Duplicate the related records.
sSQL = "INSERT INTO tInvoiceDetail ( InvoiceID, Item, Amount ) "
& _
"SELECT " & lngID & " As NewID, Item, Amount " & _
"FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail.InvoiceID = " & Me.InvoiceID & ");"
db.Execute sSQL, dbFailOnError

'Repeat for your other related table.

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

Set db = Nothing
End Sub
 
Allen,
Thanks for the prompt reply!

First question, can this be done outside the form? For
example if i pull the ID field out on a 'copy' form?

Second question, if i have 20 tables i have to run the
db.execute on all of them? and the second tier tables as
well?

thanks again for the help!

Troy.
 
You can do this outside a form, by opening a Recordset and using AddNew
directly on that.

Yes, it's usually easiest to execute an Append query statement for each of
your related tables seperately.

I can't imagine why you would have 20 separate tables (with the same
structure?), all with their own related tables.
 
You can do this outside a form, by opening a Recordset and using AddNew
directly on that.

Yes, it's usually easiest to execute an Append query statement for each of
your related tables seperately.

I can't imagine why you would have 20 separate tables (with the same
structure?), all with their own related tables.
 
The structure, has a header table (A) with 18 dependants
one to many, one of those tables (color) has two
dependant one to many tables (B), and one of those has
two dependants (C) one to many.

This database is used to build BOM's and track testing
against each part, each part has multiple colors, and
each color has multiple tests....

I'd be happy just to get accurac on level B, your first
email worked great on getting the level A tables copied.

Again, thanks for the help!

Troy
 
Back
Top