Loop Through SubForm Records In MS Access and Insert Into Table-CodePudding

I have an Access form with a subform and I'm trying to create a command that will run an Insert statement within VBA to insert data from the subform into another table. The problem I'm running into is that it duplicates the first record instead of inserting separate records. My code is below.

Private Sub Command52_Click()
Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "INSERT INTO DA_CC_QAQC_DRAWINGS_DETAIL (DRAW_ID, REV_DATE, REV_DESC, REV_TYPE)" & _
"VALUES ('" & Forms!frmASI!sfrmASI!cboDrawNumRef.Column(2) & "', Forms!frmASI.ASIDIFCDATE, Forms!frmASI!sfrmASI!ITEM_DESC, 'ASI');"

Set rs = Forms!frmASI!sfrmASI.Form.RecordsetClone

With rs
    Do Until .EOF
         DoCmd.RunSQL strSQL
        .MoveNext
    Loop
End With

End Sub

CodePudding user response：

Since the SQL code refers to controls on form and does not cycle the form records, the same data is saved. Refer to and concatenate recordset fields instead of the embedded form controls. And the SQL would have to be within the recordset loop. As is, the recordset serves no purpose.

I presume code is behind the main form.

Private Sub Command52_Click()
Dim strSQL As String
Dim rs As DAO.Recordset

Set rs = Me.sfrmASI.Form.RecordsetClone

With rs
    Do Until .EOF
        strSQL = "INSERT INTO DA_CC_QAQC_DRAWINGS_DETAIL (DRAW_ID, REV_DATE, REV_DESC, REV_TYPE)" & _
                 "VALUES ('" & Me.sfrmASI.Form.cboDrawNumRef.Column(2) & "', #" & _
                 !ASIDIFCDATE & "#, '" & !ITEM_DESC & "', 'ASI');"
         DoCmd.SetWarnings False
         DoCmd.RunSQL strSQL
         DoCmd.SetWarnings True
        .MoveNext
    Loop
End With

End Sub