Access窗体关闭时自动新增记录的VBA问题排查

我创建了两个Access窗体：一个是数据录入主窗体，另一个是特定选择触发时弹出的窗体。弹出窗体会显示与输入姓名/地址匹配的数据列表框，并自动填充自身文本框。功能正常，但关闭两个窗体后重新输入相同信息时，表格会自动新增一条基于之前数据的记录，找不到原因。

我排查了窗体代码，曾有一个将字段数据写入表的子过程，但确认该过程未被调用（调用时会将字符串字段转为大写、布尔字段设为True）。以下是相关代码：

数据录入主窗体代码 #

Private Sub cmdSave_Click()

Dim custID As Integer
Dim CustomerRS As Recordset
Dim OfficeRS As Recordset

Dim CustomerCheckSQL As String
Dim CustomerCheckRS As Recordset

Set CustomerRS = CurrentDb.OpenRecordset("CustomerDetails")
Set OfficeRS = CurrentDb.OpenRecordset("OfficeDetails")

CustomerCheckSQL = "SELECT * FROM [CustomerDetails] WHERE [CustomerName] = '" & Me.CustomerName & "' AND [StreetName] = '" & Me.StreetName & "'"
Set CustomerCheckRS = CurrentDb.OpenRecordset(CustomerCheckSQL)

If IsNull(Me.CustomerName) Or IsNull(Me.Unit) Or IsNull(Me.StreetName) Or IsNull(Me.City) Or IsNull(Me.Province) Or IsNull(Me.PostalCode) Or IsNull(Me.OfficeNumber) Or IsNull(Me.OperatorNumber) Or IsNull(Me.AdminFee) Then
    MsgBox "Please fill out all of the required fields"
Else
    If CustomerCheckRS.RecordCount >= 1 Then
        Dim answer As Integer
            answer = MsgBox("A Customer with that name and address already exist. did you want to replace(yes) or create a new record(no).", vbInformation + vbYesNo, "Cust Tracking")
            If answer = vbYes Then
                DoCmd.OpenForm "test", , , , , acDialog, Me.CustomerName & ";" & Me.StreetName & ";" & Me.Unit & ";" & Me.City & ";" & Me.Province & ";" & Me.PostalCode
            Else
                insertCustomer CustomerRS, OfficeRS
            End If
    Else
        insertCustomer CustomerRS, OfficeRS
    End If
End If
End Sub

弹出窗体代码 #

Private Sub Form_Load()
Dim criterias As String
Dim arrCriterias() As String

criterias = Nz(Me.OpenArgs, "")

arrCriterias = Split(criterias, ";")

lsCustomerData.RowSource = "SELECT * FROM CustomerDetails WHERE [CustomerName] = '" & arrCriterias(0) & "' AND [StreetName] = '" & arrCriterias(1) & "'"
lsCustomerData.Requery

Me.CustomerName.Value = lsCustomerData.Column(1, 1)
Me.StreetName.Value = lsCustomerData.Column(3, 1)
Me.Unit.Value = arrCriterias(2)
Me.City.Value = arrCriterias(3)
Me.Province.Value = arrCriterias(4)
Me.PostalCode.Value = arrCriterias(5)
End Sub

我确定问题不在insertCustomer子过程中，希望找出自动新增记录的原因。使用的是Microsoft Access 2007-2016版本。

可能的排查方向 #

• 窗体数据源绑定问题：检查主窗体/弹出窗体的Record Source属性，如果直接绑定了CustomerDetails表，Access会自动保存窗体上的未提交数据，即便你没调用手动插入的代码。建议将未绑定的窗体改为仅通过代码操作记录集，或者在窗体关闭前取消未保存的更改。
• 记录集未正确释放：代码中打开的CustomerRS、OfficeRS、CustomerCheckRS没有执行Close和Set ... = Nothing操作，未释放的对象可能导致Access保留临时状态，触发意外写入。在代码结束前添加记录集关闭逻辑。
• RecordCount判断不准确：Access的Recordset.RecordCount默认可能返回0，需要先执行CustomerCheckRS.MoveLast再CustomerCheckRS.MoveFirst，确保统计到真实的匹配记录数，避免误判为无重复而执行插入。
• 弹出窗体的自动保存：如果弹出窗体绑定了数据源，赋值文本框后可能触发BeforeUpdate事件，关闭时自动保存为新记录。检查弹出窗体的数据源设置，以及是否有未处理的更新事件。

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