Duplicate Record

**Marianna_Air** · 09-05-2012, 09:33 AM

I'm having trouble with this duplicate code. I want to duplicate the complete record except for the Vendor. Here is the code I'm using

Code:

Private Sub cmdDupe_Click___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
                !RequestDate = Date
                !RequestforQuotesID = RequestforQuotesID
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !RequestforQuotesID
            
            'Duplicate the related records: append query.
            If Me.[Request for Quotes Subform].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [INVENTORY TRANSACTIONS] ( REQUESTFORQUOTESID, ProductID, UNITESORDEREDCONV, ) " & _
                    "SELECT " & lngID & " As NewID, ProductID, Quantity, " & _
                    "FROM [INVENTORY TRANSACTIONS] WHERE REQUESTFORQUOTESID = " & Me.RequestforQuotesID & ";"
                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

**pbaldy** · 09-05-2012, 10:05 AM

It would help if you said what the "trouble" was. Offhand, there's an extra comma before FROM in the SQL.

**Marianna_Air** · 09-05-2012, 11:35 AM

I get a run time error it fails at

Code:

Private Sub Command166_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
                !EmployeeID = Me.EmployeeID
                !RequestDate = Date
                !RequestforQuotesID = GetNextSN("RFQ")
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
           lngID = !RequestforQuotesID
            
            'Duplicate the related records: append query.
            If Me.[Request for Quotes Subform].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [Inventory Transactions] (RequestforquotesID, ProductID, UnitsOrderedConv) " & _
                    "SELECT " & lngID & " As requestforquotesID, ProductID, UnitsOrderedConv " & _
                    "FROM [Inventory Transactions] WHERE requestforquotesID = " & Me.RequestforQuotesID & ";"
                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

The button will create a new record, but it does not copy the info over from my selected record

**pbaldy** · 09-05-2012, 11:45 AM

What data type is that field (and what does the function above return)?

**Marianna_Air** · 09-05-2012, 11:58 AM

The data type in that field is "text" like RFQXXXXX
The function produces a new record but without the original records info that I want to duplicate in a new record
I get a run time error 13 "Type Mismatch"
I was thinking that maybe it was looking for a long integer and not the text maybe??? If so I’m not sure how to fix that

**pbaldy** · 09-05-2012, 01:19 PM

You've declared the variable as Long, so putting a string in there will cause an error. Declare it as string (I'd change the prefix accordingly):

Dim strID As String

**Marianna_Air** · 09-06-2012, 11:20 AM

This code is still kicking my butt, I've tried everything. I'm getting an error of "too few parameters. Excpected 2." and debug highlites the code in red below

Code:

Private Sub Command169_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 strID As String      '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
                !EmployeeID = Me.EmployeeID
                !RequestDate = Date
                !RequestforQuotesID = GetNextSN("RFQ")
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
           strID = !RequestforQuotesID
            
            'Duplicate the related records: append query.
            If Me.[Request for Quotes Subform].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [Inventory Transactions] (RequestforquotesID, ProductID, UnitsOrderedConv) " & _
                    "SELECT " & strID & " As requestforquotesID, ProductID, UnitsOrderedConv " & _
                    "FROM [Inventory Transactions] WHERE requestforquotesID = " & Me.RequestforQuotesID & ";"
                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

**pbaldy** · 09-06-2012, 11:37 AM

This is a valuable tool for debugging SQL in code:

http://www.baldyweb.com/ImmediateWindow.htm

Offhand, I suspect you need to surround that value in single quotes since it's text not numeric (in both places).

**Missinglinq** · 09-07-2012, 06:09 AM

You're really over-engineering this thing, I think. This is all you really need:

Code:

Private Sub CopyPartialRecordButton_Click() Dim MyFirstField As String Dim MySecondField As String Dim MyThirdField As String 'Copy fields to variables MyFirstField = Me.FirstField MySecondField = Me.SecondField MyThirdField = Me.ThirdField 'Go to a new record DoCmd.GoToRecord , , acNewRec 'Reverse the process and plug old values into new record Me.FirstField = MyFirstField Me.SecondField = MySecondField Me.ThirdField = MyThirdField End Sub

No need to worry about Datatypes. To leave the Vendor Field blank, simply omit it from the list of Controls to populate. You can save the PK to be used as the FK on related Records (not sure why you need to do that) if you need to, and this code will leave you sitting on the newly created, partially duplicated Record.

Linq ;0)>

**Marianna_Air** · 09-11-2012, 09:49 AM

Sorry for the delay on this, I was put on another project for a few days. I like this idea but can't get it to work, it's probably something I've done incorrect. When I select the duplicate record button it does nothing

Code:

Private Sub CopyPartialRecordButton_Click()
Dim MyemployeeID As String
Dim MyOriginator As String
Dim MyRequestforQuotesDescription As String
Dim MyTransactionDescription As String
Dim MyProductName As String
Dim MyUnitsOrderedConv As String
 
'Copy fields to variables
MyemployeeID = Me.EmployeeID
MyOriginator = Me.Originator
MyRequestforQuotesDescription = Me.RequestforQuotesDescription
MyTransactionDescription = Me.TransactionDescription
MyProductName = Me.ProductName
MyUnitsOrderedConv = Me.UnitsOrderedConv

'Go to a new record
DoCmd.GoToRecord , , acNewRec
'Reverse the process and plug old values into new record
Me.EmployeeID = MyemployeeID
Me.Originator = MyOriginator
Me.RequestforQuotesDescription = MyRequestforQuotesDescription
Me.TransactionDescription = MyTransactionDescription
Me.ProductName = MyProductName
Me.UnitsOrderedConv = MyUnitsOrderedConv

End Sub

**Missinglinq** · 09-11-2012, 10:07 AM

Are you doing this from a New Record immediately after entering that Record? If so, you may need to force a Save before copying. Immediately prior to the line

'Copy fields to variables

enter

DoCmd.RunCommand acCmdSaveRecord

and see if that works. If it doesn't help, place this line in the same place

Msgbox "Click event fired!"

and see if the Messagebox shows when you click the button.

Linq ;0)>

**Marianna_Air** · 09-11-2012, 10:52 AM

no msgbox, when I input that line

**orange** · 09-11-2012, 10:57 AM

Did you try using the Debug.Print to the immediate window as Paul suggested?
Have you tried stepping through the code with F8?

**Marianna_Air** · 09-11-2012, 11:17 AM

Yes I'm stepping through it now, and its hanging up when I come to the fields that are associated with the sub_form information

Code:

Private Sub CopyPartialRecordButton_Click()
Dim MyemployeeID As String
Dim MyOriginator As String
Dim MyRequestforQuotesDescription As String
Dim MyTransactionDescription As String
Dim MyProductName As String
Dim MyUnitsOrderedConv As String

'Copy fields to variables
MyemployeeID = Me.EmployeeID
MyOriginator = Me.Originator
MyRequestforQuotesDescription = Me.RequestforQuotesDescription
MyTransactionDescription = Me.TransactionDescription
MyProductName = Me.ProductName
MyUnitsOrderedConv = Me.UnitsOrderedConv

'Go to a new record
DoCmd.GoToRecord , , acNewRec
'Reverse the process and plug old values into new record
Me.EmployeeID = MyemployeeID
Me.Originator = MyOriginator
Me.RequestforQuotesDescription = MyRequestforQuotesDescription
Me.TransactionDescription = MyTransactionDescription
Me.ProductName = MyProductName
Me.UnitsOrderedConv = MyUnitsOrderedConv
End Sub

**orange** · 09-11-2012, 11:26 AM

Where is this button located?
Are you getting an error message?
Tell us more about your Forms set up .... parent , subform1...

If you are referring to mainform with ME, then me can not refer to subform...

see http://access.mvps.org/access/forms/frm0031.htm

Duplicate Record

Thread Tools

Duplicate Record

Similar Threads

Goto Record when Duplicate Record Exists

To add a duplicate form of an existing record to a new record

Duplicate last record to next record in form

If duplicate record, go to original record

If duplicate record, go to original record

Posting Permissions