copy data from Form and Subform

Question

My Form shows only a record per time. Data are read from 2 tables ([Data Processing List] and [Attributes]). In the Form there is a Subform that gets data from a third table ([Security Measures]) and shows only the rows related to the record selected in the Form.

In the Form I have the button Copy whose aim is to duplicate the selected record.

The problem is that it duplicates only data of the 2 tables of the form and not the one of the Subform.

Did I do something wrong?

The code of the Copy button is the following (simplified):

Private Sub Copy()

            DoCmd.RunCommand acCmdSelectRecord
            DoCmd.RunCommand acCmdCopy
            DoCmd.RunCommand acCmdRecordsGoToNew
            DoCmd.RunCommand acCmdSelectRecord
            DoCmd.RunCommand acCmdPaste

End Sub

Andre · Accepted Answer · 2018-02-12T18:17:22.830

This won't stay so basic when the subform records need to be copied too - Copy/Paste always affects only the current/select record of the active form.

You need to first store the old ID, then copy the main records, read the new ID, and then use these two IDs to copy the subform records.

Something like this (air code):

Private Sub Copy()

    Dim OldId As Long, NewId As Long
    Dim S As String

    ' Read old ID
    OldId = Me!ID
    ' copy main records
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.RunCommand acCmdRecordsGoToNew
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdPaste
    ' Read new ID
    DoCmd.RunCommand acCmdSaveRecord    ' not sure if needed
    NewId = Me!ID

    ' Copy all records from old ID to new ID
    ' TODO: add missing columns
    S = "INSERT INTO [Security Measures] (ID_SM, System, Note) " & _
        "SELECT " & NewId & " AS ID_SM, System, Note " & _
        "FROM [Security Measures] WHERE ID_SM = " & OldId

    Debug.Print S
    Stop
    ' Hit Ctrl+G, copy SQL from Immediate Window to a new query

    CurrentDb.Execute S, dbFailOnError

    ' Load copied records
    Me!mySubform.Form.Requery

End Sub

BTW, [Security Measures] should really have a Primary Key on its own.

well, thanks but it doesn't work :( I get the error `syntax error in INSERT INTO statement` consider that in `[Security Measures]` the `ID_SM` is not `UNIQUE`. Could this be the problem? — , Feb 12 '18 at 15:17
Follow these steps to debug the INSERT query: [How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) . See also the edited code. — Andre, Feb 12 '18 at 18:16

score 0 · Answer 2 · answered Feb 12 '18 at 12:48

0

Or you can skip all the DoCmd stuff completely and operate directly on the recordsets of the forms:

First copy the parent record
Then copy the child records

Duplicate record and records in subform

answered Feb 12 '18 at 12:48

Gustav

53,498
7
29
55

copy data from Form and Subform

2 Answers2