Duplicate multiple records in SubForm related to single record in MainForm

[Jason M Canady]

Hi!

I am working on trying to duplicate multiple subform records in my join
table when I duplicate my mainform record.

I have created a small maintenance management database and as workorders are
completed they are marked as complete and the record is duplicated with a
new workorder number. The problem is that an individual work order may be
associated with 1or 2 pieces of equipment or with 20.

How can I recreate the associations from the original workorder and save
those records in the join table? Any ideas would be appreciated. I have
tried several ideas to no avail...

Thanks, Jason

 

[Allen Browne]

To duplicate the child records as well, you need to have the new foreign key
value (i.e. what was the new value for the primary key of the main
duplicate). JET SQL can't give you that, so use DAO to duplicate the main
form record, and then you can execute an Insert query statement to duplicate
the related records as well.

This example duplicates an invoice and the invoice detail lines. Adapt for
your needs.

Private Sub cmdDupe_Click()
Dim sSQL As String
Dim db As DAO.Database
Dim lngInvID As Long 'The new invoice key.

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
.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

 

[Jason M Canady]

Wow! I knew that it was going to be a bit harder than duplicating the
current record, but...

Thank you very much for the code. I will study it hard and see what I can
do. Unfortunately it is a bit above my abilities at this time so I probably
will not be able to impliment it at this time. It definately gives me a
starting point, and who knows, maybe I will give it a shot this week and see
what I can do... It couldn't hurt!

Thanks again. I very much appreciate your input.

Jason

 

[Allen Browne]

Okay, Jason, a quick run down on what the code is doing.

1. After delcaring its variables and getting a reference to the current
database, it saves any uncommitted changes to the current record (Me.Dirty =
False) and checks that it is not a new record (because there would be
nothing to duplicate.)

2. To duplicate the main form record, it adds a new record to the
RecordsetClone of the form. Since a form can have only one current record,
the RecordsetClone gives you a way to look at another record as well. In
this case, the clone is pointed to a new record (.Addnew), and the fields
are assigned the want to duplicate. In the example, we duplicated two
fields. The line:
!ClientID = Me.ClientID
makes the ClientID field in the clone set equal to the ClientID on the form.
Just put each field on a line of its own like that. (In this example, the
InvoiceDate of the new record is set to today instead of the InvocieDate in
the form.)

The line:
.Update
saves this new record. The code then grabs the newly assigned InvoiceID
value (it's an autonumber), because we need that for the child records.

3. Next, the code looks in the subform to see if there are any child records
to duplicate. If there are (RecordCount is greater than zero), then it
creates an Append query statement, to duplicate all the child records at
once. It selects the records from the table that the subform is based on,
using the InvoiceID (foreign key field). But the foreign key field uses uses
the newly generated InvoiceID from step 2 above.

The SQL statement is then executed.

Finally, the main form displays the new record by setting its Bookmark to
that of the RecordsetClone. Remember that the main form's clone set is
pointing to the newly generated record.

If you have difficulty in generating the SQL string you need:
1. Create a new query into the subform's table.
2. Change it to an Append query (Append on Query menu).
3. Switch to SQL View (View menu) for a look at the kind of SQL statement
you need to create.

Hope that's of benefit.

 

[Jason M Canady]

Allen, I don't think that I can thank you enough! With what you have just
explained I actually think that I will be able to impliment this code in my
project. It is a bit overwhelming to look at code sometimes (especially in
a format that I don't really understand very well) and decipher what is
happening. Comparing your description to the code I can see exactly what
you are saying.

Thanks again!! (ALOT!)

Jason