This article applies only to a Microsoft Access database (.mdb).
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 that duplicates the current order into a new order. The article then shows you how to use the new command button.
- Duplicate the main form record.
- 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.
- Save the duplicated main form record.
- 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.
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.
- Start Microsoft Access and open the sample database Northwind.mdb.
- Open the Orders form in Design view.
- Add a new command button and set the command button's properties as follows:Name: btnDuplicate
- 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.
!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
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.OpenQuery "Duplicate Order Details"
'Requery the subform to display the newly appended records.
- On the Tools menu, click References, and then click to select Microsoft DAO 3.6 Object Library. Click OK.
- On the File menu, click Close and Return to Microsoft Access.
- Save the Orders form and close it.
- Create a new query based on the Order Details table.
- On the Query menu, click Append Query. In the Append dialog box, select Order Details in the Table Name box, and then click OK.
- 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.
- 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.
- In an empty QBE grid column, create a new column by typing the following line in the Field row:NewOrderID: CLng([Forms]![Orders]![OrderID])
- 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
- 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.
- Click the new Duplicate command button.
- Note that the total number of records increases by one and that the current record is the new record that you just added.
Help menu, type subforms: what they are and how they work in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
ID d'article : 208824 - Dernière mise à jour : 23 juin 2005 - Révision : 1