Copy Records and Append Field of Newly Copied Records (1 Viewer)

[mhorner]

Hello again everybody, I am looking for guidance here on an issue that has been plaguing me for the past few years.

Question:
How do I duplicate a set of records, then select the newly duplicated records, then update a field on only the newly duplicated records?

Context:

• I have a form called frmCertificates which uses tblCertificates as the record source.
• I have a subform on frmCertificates called sbfrmModels which uses tblModels as the record source.
• Each record in tblModels contains a field called C-ID, which is populated with the primary key from the certificate record in tblCertificates for which the model apperas on that certificate. I have a button on frmCertificates which allows a user to add a model to tblModels and it automatically populates the C-ID field to use the ID from the certificate record. This allows a user to add thousands of models to a certificate record.
• Whenever a certificate is updated, we need the ability to preserve the old certificate record to create a snapshot in time. So a new certificate record has to be created every time a change is made to that certificate. This can be cumbersome to users when a certificate has thousands of models and each model has to be manually added to the new certificate. So I want to create a function that allows a user to duplicate all of the models from the old certificate record and attach them to the new certificate record.
• I can successfully duplicate the certificate record in tblCertificates.
• I can also successfully duplicate all the records in tblModels whose value in C-ID matches the ID field from duplicated Certificate record in tblCertificates.
• Now, I need to select the newly duplicated records in tblModels and append the C-ID field to match the value of the ID field from the newly created record in tblCertificates. This is where I am struggling.

At this point, I am looking for guidance specifically on how to select newly duplicated records in VBA so I can append the C-ID field. Does anybody have any good examples or support articles showing how to achieve this?

 

[bastanu]

Many ways to do this, but I would simply have a field in tblModels to use as a flag, could be a yes\no, a date field with a timestamp, a numeric (long) to hold the original ID. You populate it when you add the batch of records attached to the original certificate, then you run a update query (not append as you mentioned) to update the C-ID with the new ID then clear the flag.

Another way, which might be safer in a multi-user environment is to use a small local (front-end) table to store the original batch, you would empty it every time, then populate it with your append query , update the ID and finally append it to tblMaterials.

But, if you do what you say in the proper sequence you should be able to do it all in one as long as you have some way of avoiding interference between users; one you create the new certificate you retrieve its new ID using a DMax("ID","tblCertificates"), and you use that for the C-ID in your append query that duplicates the modules.

Cheers,

 

[ebs17]

If you implement your wish 1:1, you need exactly 2 append queries with single execution, one for each table.
C-ID is autoincrement in tblCertificates?

 

[mhorner]

Genius ideas bastanu, much appreciated input. I had never thought of these approaches. Let me tinker around in the next few days with the flag idea. I also already have the database split, so it wouldn't be too difficult to add a new table to the front end specifically to hold the batch model records until they can be updated and moved back into tblModels on the back end.

ebs17 - well the ID field autoincrements on each record in tblCertificates. The C-ID field on tblModel records is autopopulated using the autoincrement ID field from tblCertificates. The C-ID field on tblModel records is really just a field to create a relationship to the primary key on the tblCertificate record.

Anyway, I'll give some of the tips a try here and see how far I get. I appreciate the input, gentlemen.

 

[bastanu]

@mhorner: what ebs17 suggested is what is in the my final paragraph, the tricky part comes if multiple users are trying to create new certificates as the DMax (for the newly created certificate) could belong to a different certificate creates by somebody else. You could use a CreatedBy field in tblCertificates to isolate the users or use the local table method.

Cheers,

 

[ebs17]

What I implied in my statement: In tblCertificate there is a field CertificatName, which is transferred to the duplicate in exactly the same way. Is that so?

If yes, you can use it as a key to create the link old C-ID to new C-ID:

SELECT
   O.CertificatName,
   O.[C-ID] As Old_CID,
   N.[C-ID]
FROM
   (
      SELECT
         [C-ID],
         CertificatName
      FROM
         tblCertificate
      WHERE
         DateCertificat = NewDate
   ) AS N
      INNER JOIN
         (
            SELECT
               [C-ID],
               CertificatName
            FROM
               tblCertificate
            WHERE
               DateCertificat = OldDate
         ) AS O
         ON N.CertificatName = O.CertificatName

This mapping can then be used when appending to tblModels to enter the new foreign key.

 

[mhorner]

Hey guys, I know its been a few weeks, but I'm getting really close to making this work. I'm having one challenge that I suspect is probably an easy solution, but I'm really not terribly familiar with SQL, I'm still learning.

How do you copy records from Table 1 into Table 2, but have the ID (Primary Key) increment to the next autonumber in Table 2?

        'Move Updated Model Records from tblCertificatesModelsLocal back into tblCertificatesModels
        DoCmd.RunSQL ("INSERT INTO [tblCertificatesModels] SELECT * FROM [tblCertificatesModelsLocal] WHERE [Certificate Record] = " & NewCertRecordID)

 

[mhorner]

Ok I asked too soon, I think I've figured it out. For some reason, when you attempt to move records using SQL, it can't detect if a field is a primary key, and thus it tries to copy the primary key into the new table, yielding in a primary key conflict. But if you try to copy records using DoCmd, access is smart enough to detect if a field is an autonumber primary key, and will automatically generate a new primary key for each new record.

With that said, here is the final solution that I've come up with, just to share with others in case somebody else is trying to do the same thing here. I tried to take good notes so you can see what the routine is doing. I'm sure there are better ways to do it, but here's what I've got.

First things first, here are a few contextual details:

1. I am trying to copy the currently selected record from tblCertificates, which is the source of frmCertificates
2. I am also trying to copy all the Model records from tblCertificatesModels (which appear as a subtable on frmCertificates).
3. The goal is to duplicate a Certificate record as well as all Model records joined to the Certificate record
4. I created a local front end table called "tblCertificatesModelsLocal" to store the Model records, then update the joined Certificate Record field to point to the new Certificate record ID number
5. Then I copy the records from the local table back into the tblCertificates.
6. Then I requery frmCertificate to show the new records in the form.

Private Sub cmdDuplicate_Click()

    'Identify the record number of the currently selected record and store it in the variable named CurrentCertRecordID
    CurrentCertRecordID = Me.ID.Value
    Debug.Print "Current Certificate Record ID = " & CurrentCertRecordID

    'If form is empty then we cannot duplicate an empty record
    If IsNull(CurrentCertRecordID) Then
         MsgBox ("Cannot duplicate an empty record. Finish creating this record before duplicating.")
    End If


    'If form is not empty, then we can duplicate this record
    If Not IsNull(CurrentCertRecordID) Then
   
        'Ask user if they want to duplicate record
        Dim Msg, Style, Title, Help, Ctxt, Response
        Msg = "Are you sure you want to duplicate this certificate record?"
        Style = vbYesNo + vbCritical + vbDefaultButton2
        Title = "Duplicate Record"
        Help = "DEMO.HLP"
        Ctxt = 1000
        Response = MsgBox(Msg, Style, Title, Help, Ctxt)
       
        'User chose Yes.
        If Response = vbYes Then
       
            'Turn Warnings Off So The User Doesn't Have To Click 10 Yes Buttons In a Row As the Code Below Executes
            DoCmd.SetWarnings (WarningsOff)
           
            'Save current record
            DoCmd.RunCommand acCmdSaveRecord
           
            'Copy Model Records from tblCertificatesModels where C-ID equals the Certificate Record Number and past them into a new table called tblCertificatesModelsLocal
            DoCmd.RunSQL ("SELECT * INTO [tblCertificatesModelsLocal] FROM [tblCertificatesModels] WHERE [Certificate Record] = " & CurrentCertRecordID)
                       
            'Duplicate Certificate Record
            On Error Resume Next
            DoCmd.RunCommand acCmdSelectRecord
            If (MacroError = 0) Then
                DoCmd.RunCommand acCmdCopy
            End If
            If (MacroError = 0) Then
                DoCmd.RunCommand acCmdRecordsGoToNew
            End If
            If (MacroError = 0) Then
                DoCmd.RunCommand acCmdSelectRecord
            End If
            If (MacroError = 0) Then
                DoCmd.RunCommand acCmdPaste
            End If
            If (MacroError <> 0) Then
                Beep
                MsgBox MacroError.Description, vbOKOnly, ""
            End If
           
        'Identify the record number of the newly copied record and store it in the variable named NewCertRecordID
        NewCertRecordID = Me.ID.Value
        Debug.Print "New Certificate Record ID = " & NewCertRecordID
        NewCertRecordID = Me.ID.Value
       
        'Update Model Records in tblCertificateModelsLocal to make the C-ID field match the C-ID field of the newly copied certificate record.
        DoCmd.RunSQL ("UPDATE [tblCertificatesModelsLocal] SET [Certificate Record]=" & NewCertRecordID)
       
        'Select the newly created records in tblCertifictesModelsLocal and copy them into tblCertificatesModels
        DoCmd.OpenTable "tblCertificatesModelsLocal", acNormal, acEdit
        DoCmd.RunCommand acCmdSelectAllRecords
        DoCmd.RunCommand acCmdCut
        DoCmd.Close acTable, "tblCertificatesModelsLocal"
        DoCmd.OpenTable "tblCertificatesModels", acNormal, acEdit
        DoCmd.GoToRecord , , acLast
        DoCmd.RunCommand acCmdPasteAppend
        DoCmd.Close acTable, "tblCertificatesModels"
        Form.Requery
        DoCmd.GoToRecord , , acLast
       
        ' User chose No.
        Else
                MsgBox ("Certificate record was not duplicated.")
        End If
        End If
   
cmdDuplicate_Click_Exit:
    Exit Sub

cmdDuplicate_Click_Err:
    MsgBox ("Certificate record was not duplicated.")
    Resume cmdDuplicate2_Click_Exit
       
End Sub