MS Access遍历子窗体记录插入表格时重复记录问题排查

你的代码之所以重复插入第一条记录，核心问题有两个：

1. SQL语句在循环外面定义，拼接好后就固定了，循环时不会更新里面的字段值
2. 直接引用子窗体控件的当前值，而不是跟着记录集移动获取每行的数据

修复方案1：循环内动态拼接SQL #

把SQL语句放到循环内部，每次循环从当前记录集里取对应字段的值，重新拼接SQL：

Private Sub Command52_Click()
    Dim strSQL As String
    Dim rs As DAO.Recordset
    Dim drawID As String
    Dim revDate As Date
    Dim revDesc As String
    
    Set rs = Forms!frmASI!sfrmASI.Form.RecordsetClone
    
    With rs
        Do Until .EOF
            ' 处理字符串中的单引号，避免SQL语法报错
            drawID = Replace(!cboDrawNumRef.Column(2), "'", "''")
            revDate = Forms!frmASI.ASIDIFCDATE ' 主窗体固定值，无需循环更新
            revDesc = Replace(!ITEM_DESC, "'", "''")
            
            ' 每次循环重新拼接SQL，用当前行的字段值
            strSQL = "INSERT INTO DA_CC_QAQC_DRAWINGS_DETAIL (DRAW_ID, REV_DATE, REV_DESC, REV_TYPE) " & _
                     "VALUES ('" & drawID & "', #" & revDate & "#, '" & revDesc & "', 'ASI');"
            
            DoCmd.RunSQL strSQL
            .MoveNext
        Loop
    End With
    
    Set rs = Nothing ' 释放对象
    MsgBox "数据插入完成！"
End Sub

更推荐的方案2：使用参数化查询 #

参数化查询不用手动处理引号、日期格式，还能避免SQL注入风险，代码更健壮：

Private Sub Command52_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = Forms!frmASI!sfrmASI.Form.RecordsetClone
    
    ' 创建带参数的查询模板
    Set qdf = db.CreateQueryDef("", _
        "INSERT INTO DA_CC_QAQC_DRAWINGS_DETAIL (DRAW_ID, REV_DATE, REV_DESC, REV_TYPE) " & _
        "VALUES ([pDrawID], [pRevDate], [pRevDesc], 'ASI');")
    
    With rs
        Do Until .EOF
            ' 给参数赋值，直接用记录集的字段值
            qdf.Parameters("[pDrawID]") = !cboDrawNumRef.Column(2)
            qdf.Parameters("[pRevDate]") = Forms!frmASI.ASIDIFCDATE
            qdf.Parameters("[pRevDesc]") = !ITEM_DESC
            
            qdf.Execute dbFailOnError ' 执行查询，出错时会提示错误信息
            .MoveNext
        Loop
    End With
    
    ' 释放所有对象
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
    MsgBox "数据插入完成！"
End Sub

关键注意点 #

• 字符串字段里如果有单引号，必须替换成双单引号，否则会导致SQL语法错误
• Access中日期类型字段要用#包裹，这是SQL语法要求
• 用完Recordset、QueryDef等对象后，记得释放，避免内存占用

内容的提问来源于stack exchange，提问作者house