Microsoft Access Tips for Serious Users

Provided by Allen Browne, November 2005


Duplicate the record in form and subform

The example below shows how to duplicate the record in the main form, and also the related records in the subform.

Change the highlighted names to match the names of your fields, table, and subform control. To use the code as is, add a command button to the Orders form in Northwind.

The code

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
    
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                !CustomerID = Me.CustomerID
                !EmployeeID = Me.EmployeeID
                !OrderDate = Date
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !OrderID
            
            'Duplicate the related records: append query.
            If Me.[Orders Subform].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [Order Details] ( OrderID, ProductID, Quantity, UnitPrice, Discount ) " & _
                    "SELECT " & lngID & " As NewID, ProductID, Quantity, UnitPrice, Discount " & _
                    "FROM [Order Details] WHERE OrderID = " & Me.OrderID & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
End Sub

Explanation

The code first saves any edits in progress, and checks that the form is not at a new record.

The AddNew assigns a buffer for the new record. We then copy some sample fields from the current form into this buffer, and save the new record with Update.

Ensuring the new record is current (by setting the recordset's bookmark to the last modified one), we store the new primary key value in a variable, so we can use it in the related records.

Then, we check that there are records in the subform, and duplicate them with an append query statement. The query selects the same child records shown in the subform, and appends them to the same table with the new OrderID. If you are not sure how to create this query statement for your database, you can see an example by mocking up a query and switching to SQL view (View menu, in query design.)

So why did we use AddNew in the main form, but an append query statement to duplicate the subform records?


Home Index of tips Top