Duplicate Parent and Child Records

[philbullock1223]

I need to duplicate a record in a table. I have been successful in duplicating the parent record with this code:

DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdCopy
        DoCmd.RunCommand acCmdRecordsGoToNew
        DoCmd.RunCommand acCmdSelectRecord
        DoEvents
        DoCmd.RunCommand acCmdPaste
        DoCmd.RunCommand acCmdSaveRecord

I would like to apply the same code somehow to also duplicate the Parent's associated child records.

I tried this code, with no luck:

DoCmd.OpenTable "ChildTable"
    DoCmd.ApplyFilter , "ID = " & ID
    DoCmd.RunCommand acCmdSelectAllRecords
    DoCmd.RunCommand acCmdCopy
    DoCmd.RunCommand acCmdRecordsGoToNew
    DoEvents
    DoCmd.RunCommand acCmdPaste
    DoCmd.RunCommand acCmdSaveRecord

Note: one of the fields in the child table is an "attachment" so an SQL INSERT INTO doesn't seem to work for me.

Thanks in advance for your help

 

[jdraw]

Could you give readers some rationale for this activity? It isn't the most common request.

 

[pbaldy]

Does this help?

http://allenbrowne.com/ser-57.html

 

[philbullock1223]

No problem jdraw. Many records in my recordset don't differ by much, so having a "duplicate" button is valuable for quick entry. With that said, I also want this "duplicate" button to duplicate the child records in another few tables also.

In it's simplest form, you can think of me needing a "duplicate" button for a product, but I also want the corresponding table of ingredients, suppliers, and customers to copy also.

Hopefully that explains it enough. Thanks!

 

[philbullock1223]

pbaldy, thanks so much for the input, but unfortunately this method won't work because it loops through the record to copy. I have a set of code that does this effectively for almost all records. Unfortunately, when I have an attachment field, this method seems to fall apart.

For example, this code worked for me fine for the ID field and the Description field. I could not get it to work for the attachment field.

Set ChildQ = CurrentDb.OpenRecordset("SELECT * FROM Child WHERE ID = " & ID, dbOpenDynaset)

If ChildQ.RecordCount > 0 Then DoCmd.RunSQL "INSERT INTO Child (ID, Description) VALUES ('" & ID & "', '" & ChildQ.Description & "');"

 

[pbaldy]

Not sure what you mean by looping, as that code doesn't loop. In any case, I haven't used attachment fields (won't), so not sure how to copy them if Allen's code doesn't work.

 

[philbullock1223]

Sorry, I didn't mean looping. I meant that it uses the "INSERT INTO" command which I have already tried. Thanks for the suggestion tho pbaldy. Much appreciated!

 

[philbullock1223]

I found my solution at this link: http://www.informedcorp.com/Access-Development-developers-page.html

The Data Mover sample database was exactly what I needed! Huge help!