Make copy of records into same table

[santosm]

Hi All,
What would be the best way to create a copy of records into the same table as new entries? In other words, I can go get the old records and then want to create a new set again in the table.

Thanks,
Mark

[HansV]

If you want to do this directly in the table:

- Select one or more records using the record selector at the left hand of the records (you can drag the mouse across them, or click one record, then Shift+click another).
- Press Ctrl+C to copy.
- Select the new record at the bottom of the table.
- Press Ctrl+V.

If the table has an AutoNumber primary key field, it will automatically assign new values to the pasted records. If you have other unique keys, you won't be able to paste the records.

Another option is to create an append query that appends records to the same table.

[santosm]

Hi Hans,
I should have been more clear! I want to do this in code when the user clicks the "New Record" button. In this case, the top level record has 1 to x number of items related to it from the table in question. I create the new top level record and want to automatically create a new set of sub-items based on what was there before.

Thanks,
Mark

[HansV]

Please provide more detailed information about the "parent table" and the "child table".

[santosm]

Hi Hans,
I can go get the existing records from the child table and simply just want to just create a new set of them in the same table. Similar to CTRL(C/V) directly in the table, but using code. Does that make better sense.

Thanks,
Mark

[HansV]

It makes sense but it doesn't provide any more details...

I assume that you have a main form and a subform.
Do you want to duplicate the current record in the main form, and at the same time duplicate the related records in the subform?

If so, please tell me:
- The name of the link field on the main form.
- The name of the link field on the subform.
- The name of the table that contains the records in the subform

If not, what then?

[santosm]

Hi Hans,
Here is the complete code if the "New Record" button. As a note the contract is the parent and the equip is the child:

Code: Select all

Private Sub Command12_Click()
On Error GoTo ErrHandler
Dim db As Database, rstNewRec As DAO.Recordset, iEquip As Long, iContact1 As Long, iContractID As Long
Dim iCustChoice As Integer, iCustID As Long, iCustContact As Long, iOldContractID As Long


    iCustChoice = MsgBox("Would you like to create a new record for this customer? Press No to create new " _
    & "record for another customer or cancel.", vbYesNoCancel)
    
        If iCustChoice = 6 Then 'YES
            iOldContractID = Me.Text0
        '***** Create new contract entry *****
            iCustID = Me.Text36
            Set db = CurrentDb
            Set rstNewRec = db.OpenRecordset("tContracts")
            With rstNewRec
                .AddNew
                If Not IsNull(Me.Text38) Then
                    !Contact = Me.Text38.Value
                End If
                !Ship_ID = Me.Text36
                !Contract_Type = 1
                !Contract_Status = 0
                !Initial_Email_Sent = 0
                .Update
                .MoveLast
                iContract = !ID
                .Close
                Form_Current
            End With
            Set db = Nothing
            Set rstNewRec = Nothing
        '************************************
        
        '***** Create Equip Entries ******
        Dim src As Recordset, dest As Recordset
        'Dim i As Integer
        
        Set src = CurrentDb.OpenRecordset("Select * from tContracts_eq where contract_id = " & iOldContractID, dbOpenDynaset)
        Set dest = CurrentDb.OpenRecordset("Select * from tContracts_eq;", dbOpenDynaset)
        
        src.MoveFirst
        Do Until src.EOF
            dest.AddNew
            dest!Contract_id = iContract
            dest!equip_id = src!equip_id
            dest.Update
            src.MoveNext
        Loop
        '*********************

        ElseIf iCustChoice = 7 Then
            '**open form to add new contract with customer selection
        End If
    
            

Exit Sub

ErrHandler:
MsgBox Err.Description
Resume Next

End Sub

I am working with the " '***** Create Equip Entries ******" section to create the equip records (child) but it is telling me that doing so would create some duplicate key problems, etc.

Thanks,
Mark

[HansV]

Thanks, I'll get back to you after dinner.

[santosm]

Cool thanks!

[HansV]

Does this work? If not, I'd have to see a copy of the database with some dummy records, without sensitive data.

Code: Select all

Private Sub Command12_Click()
    Dim db As DAO.Database, rstNewRec As DAO.Recordset
    Dim iCustChoice As Integer, iNewContractID As Long, iOldContractID As Long

    On Error GoTo ErrHandler

    iCustChoice = MsgBox("Would you like to create a new record for this customer? " & _
        "Press No to create a new record for another customer or cancel.", vbYesNoCancel)

    Select Case iCustChoice
    Case vbYes
        iOldContractID = Me.Text0
        '***** Create new contract entry *****
        Set db = CurrentDb
        Set rstNewRec = db.OpenRecordset("tContracts", dbOpenDynaset)
        With rstNewRec
            .AddNew
            iNewContractID = !ID
            If Not IsNull(Me.Text38) Then
                !Contact = Me.Text38.Value
            End If
            !Ship_ID = Me.Text36
            !Contract_Type = 1
            !Contract_Status = 0
            !Initial_Email_Sent = 0
            .Update
            .MoveLast
            .Close
            Form_Current
        End With
        Set rstNewRec = Nothing
        '************************************

        '***** Create Equip Entries ******
        Dim strSQL As String
        strSQL = "INSERT INTO tContracts_eq (Contract_id, equip_id) " & _
            "SELECT " & iNewContractID & ", equip_id FROM tContracts_eq " & _
            "WHERE contract_id=" & iOldContractID
        db.Execute strSQL, dbFailOnError
        '*********************
        Set db = Nothing

    Case 7
        '**open form to add new contract with customer selection
    End Select

    Exit Sub

ErrHandler:
    MsgBox Err.Description
End Sub