Access通过ComboBox的AfterUpdate事件批量填充子表单多字段求助

你当前的代码仅能修改子表单当前处于激活状态的单条记录，要批量更新当前TransectOID对应的所有子表单记录，可参考以下两种方案：

方案1：遍历子表单记录集（推荐新手使用，无需修改SQL语句） #

该方案直接操作子表单的记录集，不会改变用户当前在子表单的选中位置，更新效果实时可见：

Private Sub DataObs_AfterUpdate()
    Dim rs As DAO.Recordset
    Set rs = Me.frmLPIDetail.Form.RecordsetClone
    
    If Not (rs.BOF And rs.EOF) Then
        rs.MoveFirst
        Do Until rs.EOF
            rs.Edit
            rs!Data_observer = Me.DataObs
            rs.Update
            rs.MoveNext
        Loop
    End If
    
    rs.Close
    Set rs = Nothing
    Me.Dirty = False
    Me.frmLPIDetail.Form.Requery
End Sub

Private Sub DataRec_AfterUpdate()
    Dim rs As DAO.Recordset
    Set rs = Me.frmLPIDetail.Form.RecordsetClone
    
    If Not (rs.BOF And rs.EOF) Then
        rs.MoveFirst
        Do Until rs.EOF
            rs.Edit
            rs!Data_recorder = Me.DataRec
            rs.Update
            rs.MoveNext
        Loop
    End If
    
    rs.Close
    Set rs = Nothing
    Me.Dirty = False
    Me.frmLPIDetail.Form.Requery
End Sub

方案2：直接执行SQL更新（适合SQL Server后端，效率更高） #

该方案直接向后端数据库发送更新指令，适合数据量较大的场景，需将代码中的[你的子表实际名称]替换为你绑定子表单的SQL Server表名：

Private Sub DataObs_AfterUpdate()
    Dim strSQL As String
    ' 转义文本中的单引号避免SQL报错
    Dim obsVal As String
    obsVal = Replace(Me.DataObs.Value, "'", "''")
    
    ' 若TransectOID为文本类型，需要将值包裹在单引号内
    strSQL = "UPDATE [你的子表实际名称] SET Data_observer = '" & obsVal & "' " & _
             "WHERE TransectOID = " & Me.TransectOID.Value
    
    CurrentDb.Execute strSQL, dbFailOnError
    Me.frmLPIDetail.Form.Requery
    Me.Dirty = False
End Sub

Private Sub DataRec_AfterUpdate()
    Dim strSQL As String
    Dim recVal As String
    recVal = Replace(Me.DataRec.Value, "'", "''")
    
    strSQL = "UPDATE [你的子表实际名称] SET Data_recorder = '" & recVal & "' " & _
             "WHERE TransectOID = " & Me.TransectOID.Value
    
    CurrentDb.Execute strSQL, dbFailOnError
    Me.frmLPIDetail.Form.Requery
    Me.Dirty = False
End Sub

注意事项 #

• 执行更新前可添加If Me.frmLPIDetail.Form.Dirty Then Me.frmLPIDetail.Form.Dirty = False，先保存子表单中未提交的修改，避免冲突
• 若TransectOID为文本类型，需要将SQL语句中TransectOID = " & Me.TransectOID.Value修改为TransectOID = '" & Me.TransectOID.Value & "'

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