Access VBA设置二级嵌套子窗体复选框为True时rs2空记录集问题

问题根因 #

你定义strAccComp SQL语句的时机错误：

• 代码开头拼接strAccComp时，orddetid还未赋值，默认值为0，SQL语句会被固定为WHERE OrderDetailID = 0
• 后续循环中哪怕你给orddetid赋了当前订单明细的正确ID，已经生成的strAccComp字符串不会自动更新，因此rs2永远查询不到对应配件记录。

修正方案 #

把strAccComp的拼接逻辑移到orddetid赋值之后，每次循环都重新生成对应当前订单明细ID的查询语句即可，同时补充了记录集资源释放的逻辑避免内存泄漏。

修正后完整代码 #

Private Sub IsComplete_AfterUpdate()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim strItemComp As String
    Dim strAccComp As String
    Dim ordid As Long
    Dim orddetid As Long

    ordid = Me.txtOrdID
    strItemComp = "SELECT OrderDetailID, IsComplete, CompDate FROM tblOrderDetails WHERE OrderID = " & ordid
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strItemComp)

    If Me.IsComplete = True Then
        If MsgBox("Marking main order complete will mark ALL items and accessories for this Order as complete!", vbYesNo, "Are you sure?") = vbYes Then
            Me!txtCompletionDate = Date
            rs.MoveFirst
            Do Until rs.EOF
                If rs!IsComplete = False Then
                    rs.Edit
                    rs!IsComplete = True
                    rs!CompDate = Date
                    rs.Update
                End If

                orddetid = rs.Fields("OrderDetailID").Value
                Debug.Print orddetid
                ' 把SQL拼接移到此处，每次用当前的orddetid生成查询语句
                strAccComp = "SELECT OrderAccID, IsComplete, CompDate FROM tblOrderAcc WHERE OrderDetailID = " & orddetid
                Set rs2 = db.OpenRecordset(strAccComp)
                If rs2.RecordCount > 0 Then
                    rs2.MoveFirst
                    Do Until rs2.EOF
                        If rs2!IsComplete = False Then
                            rs2.Edit
                            rs2!IsComplete = True
                            rs2!CompDate = Date
                            rs2.Update
                        End If
                        rs2.MoveNext
                    Loop
                End If
                ' 用完释放二级子表记录集资源
                rs2.Close
                Set rs2 = Nothing

                rs.MoveNext
            Loop
            ' 释放一级子表、数据库对象资源
            rs.Close
            Set rs = Nothing
            Set db = Nothing
            Me.Dirty = False
            Exit Sub
        Else
            Me.Undo
        End If
    Else
        Me.txtCompletionDate = Null
        Exit Sub
    End If
    Me.Dirty = False
End Sub

可选优化建议 #

你可以直接用批量更新SQL替代循环遍历记录集，执行效率更高，代码也更简洁：

' 批量更新当前工单下所有订单明细
db.Execute "UPDATE tblOrderDetails SET IsComplete=True, CompDate=Date() WHERE OrderID=" & ordid & " AND IsComplete=False", dbFailOnError
' 直接批量更新当前工单下所有订单明细关联的配件，不需要循环查询每一个明细ID
db.Execute "UPDATE tblOrderAcc SET IsComplete=True, CompDate=Date() WHERE OrderDetailID IN (SELECT OrderDetailID FROM tblOrderDetails WHERE OrderID=" & ordid & ") AND IsComplete=False", dbFailOnError

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