vba - 更新现有记录并在表中添加新记录 - 仅更新一条记录（MS Access VBA）

问题描述

我最近读了这篇文章：更新现有记录并在表中添加新记录（MS Access VBA）

一个经过深思熟虑和优雅的解决方案，但在我的实现中，只有 tblTempData 表的第一条记录被添加（或更新，如果存在）到 tblCommon 表。

原始帖子的完整代码是：

Option Explicit

Private rsCommon As DAO.Recordset

Public Sub UpdateExistingRecords()
    On Error GoTo ErrTrap

    Dim rs As DAO.Recordset
    Set rs = CurrentDb().OpenRecordset("SELECT * FROM tblTempData", dbOpenSnapshot)
    Set rsCommon = CurrentDb().OpenRecordset("SELECT * FROM tblCommon", dbOpenDynaset)

    Dim idx As Long
    For idx = 1 To rs.RecordCount
        If ExistsInCommon(rs![Item ID]) Then
            If Not Update(rs) Then
                MsgBox "Failed to update.", vbExclamation
                GoTo Leave
            End If
        Else
            If Not Add(rs) Then
                MsgBox "Failed to add.", vbExclamation
                GoTo Leave
            End If
        End If
        rs.MoveNext
    Next

Leave:
    If Not rs Is Nothing Then rs.Close
    If Not rsCommon Is Nothing Then rsCommon.Close
    Set rs = Nothing
    Set rsCommon = Nothing
    Exit Sub

ErrTrap:
    MsgBox Err.Description, vbCritical
    Resume Leave
End Sub

' Exists - 'Assumes Id is String
Private Function ExistsInCommon(ByVal Id As String)
    ExistsInCommon = DCount("*", "tblCommon", "[Item ID] = '" & Id & "'") > 0   
End Function

' Update
Private Function Update(rs As DAO.Recordset) As Boolean
    With rsCommon
        .FindFirst "[Item ID] = '" & rs![Item ID] & "'"
        If .NoMatch Then Exit Function
        .Edit
        ![Item Description] = rs![Item Description]
        ![Material Number] = rs![Material Number]
        ![User] = rs![User]
        ![Supplier] = rs![Supplier]
        ![Current Status] = rs![Current Status]
        ![Remarks] = rs![Remarks]
        .Update
        .MoveFirst
    End With
    Update = True
End Function

'Add
Private Function Add(rs As DAO.Recordset) As Boolean
    With rsCommon
        .AddNew
        ![Item Description] = rs![Item Description]
        ![Material Number] = rs![Material Number]
        ![User] = rs![User]
        ![Supplier] = rs![Supplier]
        ![Current Status] = rs![Current Status]
        ![Remarks] = rs![Remarks]
        ![Item ID] = rs![Item ID]
        .Update
    End With
    Add = True
End Function

在进一步审查中，我认为 tblTempData 的记录计数代码存在问题

Dim rs As DAO.Recordset

Set rs = CurrentDb().OpenRecordset("SELECT * FROM tbl_kpi_data_temp", dbOpenSnapshot)
Set rsCommon = CurrentDb().OpenRecordset("SELECT * FROM tbl_kpi_data_leonardo", dbOpenDynaset)

Dim idx As Long
For idx = 1 To rs.RecordCount

当我的 tblTempData 中有更多记录时，这将为我返回 1。如果我用固定值替换 rs.RecordCount（出于测试目的），我会得到我硬编码的记录数，并按预期添加或更新了固定值。

有任何想法吗？

非常感谢

标签： vbams-accessinsert-update

vba - 更新现有记录并在表中添加新记录 - 仅更新一条记录（MS Access VBA）

问题描述

解决方案

推荐阅读