Access VBA中NotInList事件触发时如何自动为CustomerID字段赋值

核心修改逻辑 #

• 原代码遍历tbl_CustomerLocations表字段时，所有非首列字段都会弹出输入框询问取值，我们仅需在循环内新增字段判断规则：当遍历到最后一个关联字段CustomerID时，直接读取当前窗体cbo_Customers组合框的选中值，跳过输入框交互
• 新增cbo_Customers选中值非空校验，避免写入空值引发外键关联错误

修改后的完整代码 #

Private Sub cbo_CustomerLocationID_NotInList(NewData As String, Response As Integer)
    ' 建议模块顶部添加Option Explicit，可提前捕获未声明变量问题
    Dim oRS As DAO.Recordset, i As Integer, sMsg As String
    Dim String_Value As String, MsgBold As String, MsgNormal As String

    Response = acDataErrContinue
    
    String_Value = Me.cbo_CustomerLocationID.Text
    
    MsgBold = String_Value
    MsgNormal = "是否将该位置添加到列表？"
    
    ' 先校验客户是否已选择，避免关联字段为空报错
    If IsNull(Me.cbo_Customers) Then
        MsgBox "请先选择对应客户，再添加新位置", vbExclamation, "操作提示"
        Exit Sub
    End If
    
    If Eval("MsgBox ('" & MsgBold & vbNewLine _
    & "@" & MsgNormal & "@@', " & vbYesNo & ", '新增位置')") = vbYes Then
        Set oRS = CurrentDb.OpenRecordset("tbl_CustomerLocations", dbOpenDynaset)
        oRS.AddNew
        oRS.Fields(1) = NewData
        For i = 2 To oRS.Fields.Count - 1
            ' 判断是否为最后一个字段CustomerID
            If i = oRS.Fields.Count - 1 Then
                ' 直接填充当前选中的客户ID，跳过输入框
                oRS(i).Value = Me.cbo_Customers.Value
            Else
                ' 其他字段保留原有输入询问逻辑
                sMsg = "请输入" & oRS(i).Name & "的取值"
                oRS(i).Value = InputBox(sMsg, , oRS(i).DefaultValue)
            End If
        Next i
        oRS.Update
        cbo_CustomerLocationID = Null
        cbo_CustomerLocationID.Requery
        DoCmd.OpenTable "tbl_CustomerLocations", acViewNormal, acReadOnly
        Me.cbo_CustomerLocationID.Text = String_Value
    End If
End Sub

改动说明 #

你仅需要替换原有循环片段的代码即可实现需求：当遍历到最后一个CustomerID字段时，程序会自动读取当前窗体上cbo_Customers的选中值，不再弹出输入框询问。

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