ACC2000: How to Duplicate a Main Form and Its Subform Detail Records (208824)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. In order to duplicate a main form record and its associated subform detail records, the following process must be automated:

1. Duplicate the main form record.
2. Set a unique primary key field value or values in the duplicated main form record. If the primary key is an AutoNumber field, this is not required.
3. Save the duplicated main form record.
4. Create and run an append query that selects detail records with the source main form record's primary key value, and that appends these records back to the detail table using the primary key value from the duplicated main form record.

It is important that the duplicated main form record is saved so that the detail records being duplicated with the append query have a main form record to be associated with. This avoids referential integrity problems.

To duplicate a main form record and its associated subform detail records, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Open the Orders form in Design view.
3. Add a new command button and set the command button's properties as follows:

   Name: btnDuplicate
   Caption: Duplicate
   

4. Click the Build button to the right of the command button's OnClick property, then click Code Builder, and then type the following procedure:

   NOTE: The first and last lines of the procedure are created by Access for you.

   Private Sub btnDuplicate_Click ()
   Dim dbs As DAO.Database, Rst As DAO.Recordset
   Dim F As Form
   
   ' Return Database variable pointing to current database.
   Set dbs = CurrentDb
   Set Rst = Me.RecordsetClone
   
   On Error GoTo Err_btnDuplicate_Click
   
   ' Tag property to be used later by the append query.
   Me.Tag = Me![OrderID]
   
   ' Add new record to end of Recordset object.
   With Rst
      .AddNew
         !CustomerID = Me!CustomerID
         !EmployeeID = Me!EmployeeID
         !OrderDate = Me!OrderDate
         !RequiredDate = Me!RequiredDate
         !ShippedDate = Me!ShippedDate
         !ShipVia = Me!ShipVia
         !Freight = Me!Freight
         !ShipName = Me!ShipName
         !ShipAddress = Me!ShipAddress
         !ShipCity = Me!ShipCity
         !ShipRegion = Me!ShipRegion
         !ShipPostalCode = Me!ShipPostalCode
         !ShipCountry = Me!ShipCountry
      .Update                     ' Save changes.
      .Move 0, .LastModified
   End With
   Me.Bookmark = Rst.Bookmark
   
   ' Run the Duplicate Order Details append query which selects all
   ' detail records that have the OrderID stored in the form's
   ' Tag property and appends them back to the detail table with
   ' the OrderID of the duplicated main form record.
   
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "Duplicate Order Details"
   DoCmd.SetWarnings True
   
   'Requery the subform to display the newly appended records.
   Me![Orders Subform].Requery
   
   Exit_btnduplicate_Click:
   Exit Sub
   
   Err_btnDuplicate_Click:
   MsgBox Error$
   Resume Exit_btnduplicate_Click:
   End Sub
   					

5. On the Tools menu, click References, and then click to select Microsoft DAO 3.6 Object Library. Click OK.
6. On the File menu, click Close and Return to Microsoft Access.
7. Save the Orders form and close it.
8. Create a new query based on the Order Details table.
9. On the Query menu, click Append Query. In the Append dialog box, select Order Details in the Table Name box, and then click OK.
10. Double-click the title bar of the Order Details field list to select all the fields in the list. Drag the fields to the first column of the QBE grid.
11. Delete OrderID from the Append To row of the OrderID column and type the following line in the Criteria row:

    [Forms]![Orders].[Tag]

    This criteria selects the detail records where the OrderID is the value found in the Orders form's Tag property, the source OrderID.
12. In an empty QBE grid column, create a new column by typing the following line in the Field row:

    NewOrderID: CLng([Forms]![Orders]![OrderID])

13. In the new column, type the following line in the Append To row:

    [OrderID]

    and then save the query as Duplicate Order Details and close it.
    
    This ensures that the selected detail records are appended back to the Order Details table with the OrderID field set to the OrderID of the new, duplicated, main form Order record.

Using the New Orders Form Duplicate Command Button

1. Open the Orders form in Form view and move to an order that contains a number of detail records. Note the record position number and the total number of records that appear in the navigation buttons.
2. Click the new Duplicate command button.
3. Note that the total number of records increases by one and that the current record is the new record that you just added.