Access数据库UsedPartsTbl重复记录增量更新代码异常求助

核心需求 #

从InvPartsTbl和WrckPrtsTbl两个表（主键均为Auto Number类型）向UsedPartsTbl同步记录：

• 若UsedPartsTbl已存在相同PartID的记录，就把QuantityUsed字段值加1
• 若不存在，则新增记录，QuantityUsed设为1

原代码的问题 #

1. 循环提前终止：Else分支里的Exit loop（语法错误，应为Exit Do）导致第一次新增记录就直接退出循环，后续记录完全没处理
2. 操作对象错误：原代码错误地在源表InvPartsTbl里做编辑/新增操作，而非目标表UsedPartsTbl
3. 匹配逻辑混乱：DLookup的判断逻辑有误，且仅处理了InvPartsTbl，完全没涉及WrckPrtsTbl
4. 仅处理单条记录：原代码只获取了列表控件的第一行数据，未遍历所有待提交的记录

修正后的代码 #

Sub CommitToInventory_Click()
    Dim db As DAO.Database
    Dim rsUsedParts As DAO.Recordset
    Dim rsWrck As DAO.Recordset
    Dim pStrge As String
    Dim ID_cp As Long
    Dim i As Integer
    
    Set db = CurrentDb
    pStrge = "Invoiced Stock"
    
    ' 遍历UsedPartsList列表控件的所有待提交记录
    With Me.UsedPartsList
        For i = 0 To .ListCount - 1
            ID_cp = .Column(0) ' 获取当前行的PartID
            
            ' 查询UsedPartsTbl中是否存在该PartID的记录
            Set rsUsedParts = db.OpenRecordset("SELECT * FROM UsedPartsTbl WHERE PartID = " & ID_cp, dbOpenDynaset)
            
            If Not rsUsedParts.EOF Then
                ' 存在则更新QuantityUsed
                rsUsedParts.Edit
                rsUsedParts!QuantityUsed = rsUsedParts!QuantityUsed + 1
                rsUsedParts.Update
            Else
                ' 不存在则新增记录
                rsUsedParts.AddNew
                rsUsedParts!PartID = .Column(0)
                rsUsedParts!PartName = .Column(1)
                rsUsedParts!Source = .Column(2)
                rsUsedParts!QuantityUsed = 1
                rsUsedParts!Guarantor = .Column(3)
                rsUsedParts!Storage = pStrge
                rsUsedParts!StorageDate = .Column(5)
                rsUsedParts!Description = .Column(6)
                rsUsedParts.Update
            End If
            rsUsedParts.Close
        Next i
    End With
    
    ' 处理WrckPrtsTbl的同步逻辑（按需调整字段映射）
    Set rsWrck = db.OpenRecordset("WrckPrtsTbl", dbOpenDynaset)
    Do While Not rsWrck.EOF
        ID_cp = rsWrck!ID ' WrckPrtsTbl的Auto Number主键
        Set rsUsedParts = db.OpenRecordset("SELECT * FROM UsedPartsTbl WHERE PartID = " & ID_cp, dbOpenDynaset)
        
        If Not rsUsedParts.EOF Then
            rsUsedParts.Edit
            rsUsedParts!QuantityUsed = rsUsedParts!QuantityUsed + 1
            rsUsedParts.Update
        Else
            rsUsedParts.AddNew
            ' 以下字段需根据WrckPrtsTbl的实际结构调整
            rsUsedParts!PartID = rsWrck!ID
            rsUsedParts!PartName = rsWrck!PartName
            rsUsedParts!Source = "拆车件" ' 标记来源
            rsUsedParts!QuantityUsed = 1
            rsUsedParts!Guarantor = rsWrck!Guarantor
            rsUsedParts!Storage = pStrge
            rsUsedParts!StorageDate = Date()
            rsUsedParts!Description = rsWrck!Description
            rsUsedParts.Update
        End If
        rsUsedParts.Close
        rsWrck.MoveNext
    Loop
    rsWrck.Close
    
    ' 释放资源
    Set rsUsedParts = Nothing
    Set rsWrck = Nothing
    Set db = Nothing
    
    MsgBox "记录同步完成！"
End Sub

关键修改点 #

1. 修正操作目标：把编辑/新增的对象改为UsedPartsTbl，彻底解决原代码改错表的问题
2. 修复循环逻辑：移除错误的Exit loop，用For循环遍历列表控件所有行，确保所有记录都被处理
3. 优化匹配逻辑：用SELECT语句直接查询目标表，比DLookup更高效且逻辑清晰
4. 扩展支持双源表：新增WrckPrtsTbl的同步逻辑，满足两个源表的处理需求
5. 规范资源释放：添加对象释放代码，避免内存泄漏

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