ACC: How to Duplicate Main Form and Its Subform Detail Records

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

The Command Button Wizard provides a Duplicate Record command button that you can use to duplicate the current record; however, this command button does not duplicate any detail of the records associated with that record that may appear in a subform.

This article describes how you can automate the duplication of a main form record and its associated subform detail records. The article shows you how to add a command button to the Orders form of the sample database Northwind.mdb (or NWIND.MDB in version 2.0) that duplicates the current order into a new order. The article then shows you how to use the new command button.

MORE INFORMATION

In order to duplicate a main form record and its associated subform detail records, the following process must be automated:

1. The main form must be duplicated.

2. Unique, primary key field value(s) must be solicited and set into the duplicated main form record. If the primary key is a counter field, this is not required.

3. The duplicated main form must be saved.

4. An append query must be run 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.

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

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or NWIND.MDB in version 2.0). You may want to back up the Northwind.mdb file, or perform these steps on a copy of the Northwind database.

1. Open the sample database Northwind.mdb and open the Orders form in Design view.

2. Add a new command button and set the command button's properties as follows:

         Name: btnDuplicate
         Caption: Duplicate
         OnClick: [Event Procedure]
   

3. Click the Build button to the right of the command button's OnClick property and type the following procedure.

   In Microsoft Access version 7.0 and Microsoft Access 97:

           Private Sub btnDuplicate_Click ()
           Dim dbs As Database, Rst As 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
   
      In Microsoft Access Version 2.0:
   
      NOTE: In the following sample code, an underscore (_) at the end of
      a line is used as a line-continuation character. Remove the
      underscore from the end of the line when re-creating this code.
   
           Sub btnDuplicate_Click ()
              Dim Msg As String
              Dim NewOrderID As Variant
              Dim Criteria As String
   
              ' Trap any unexpected error that may occur.
              On Error GoTo Err_btnDuplicate_Click
   
              ' Prompt for a unique Order ID number.
              While NewOrderID = ""
                 Msg = "Enter a unique Order ID for the duplicated record"
                 NewOrderID = InputBox(Msg)
   
                 ' If the user pressed Cancel, then exit sub.
                 If NewOrderID = "" Then Exit Sub
   
                 ' Make sure the number has not been used yet.
                 Criteria = "[Order ID]=" & NewOrderID
                 If Not IsNull(DLookup("[Order ID]", "Orders", Criteria)) Then
                    MsgBox "The specified Order ID already exists!"
                    NewOrderID = ""
                 End If
              Wend
   
              ' Record the current Order ID (primary key value) into the
              ' form's Tag property to be used latter by the append query.
              Me.Tag = Me![Order ID]
   
              ' Duplicate the current main form record:
              '    Select Record, Copy Record, Paste Append Record.
   
              DoCmd DoMenuItem A_FORMBAR, A_EDITMENU, _
                 A_SELECTRECORD_V2, , A_MENU_VER20
              DoCmd DoMenuItem A_FORMBAR, A_EDITMENU, A_COPY, , A_MENU_VER20
              DoCmd DoMenuItem A_FORMBAR, A_EDITMENU, 6, , A_MENU_VER20
   
              ' Insert the new Order ID into the duplicated main form record.
              Me![Order ID] = NewOrderID
   
              ' Save the duplicated main form record.
              DoCmd DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD, , A_MENU_VER20
   
              ' Run the Duplicate Order Details append query which selects all
              ' detail records that have the Order ID stored in the form's
              ' Tag property and appends them back to the detail table with
              ' the Order ID 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
   
   

4. Create a new query that includes the Order Details table.

5. On the Query menu, click Append Query (or Append in versions 2.0 and 7.0). Select Order Details from the Table Name box, and then click OK.

6. In the top half of the Query window, double-click the title bar of the Order Details table list to select all the fields in the list. Drag the fields to the first column of the QBE grid.

7. NOTE: In steps 7-9, replace the word "OrderID" (without the quotation marks) with the words "Order ID" (without the quotation marks) in Microsoft Access 2.0.

   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.

8. In an empty QBE grid column, create a new column by typing the following line in the Field row:

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

9. In the new column, type the following line in the Append To row, and then save the query with the name Duplicate Order Details

         [OrderID]
   

   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. In Microsoft Access 2.0, type a unique Order ID number, perhaps 123, and then click OK.

   Note that the total number of records increases by one and that the current record is the new record that you just added.

REFERENCES