VBA校验MS Access采购交货数量（解决编辑场景误判）

环境与业务背景 #

• 运行环境：MS Access 前端 + SQL Server 2019 后端
• 业务场景：采购订单交货管理，需实现校验规则：累计交货总数量不得超过对应采购明细的订购总数量

示例数据结构 #

采购订单明细表（PurchaseOrderDetail） #

交货主表（Delivery） #

交货明细表（DeliveryDetail） #

示例数据说明：对应采购明细共订购10件商品，分两次交货，第一次交5件、第二次交3件，累计交货8件，属于部分交货状态。

原有代码与问题 #

现有表单txtQuantity（交货数量输入框）的BeforeUpdate事件原有VBA代码如下：

Private Sub txtQuantity_BeforeUpdate(Cancel As Integer)

    Dim QtyOrdered, QtyDelivered, LeftToDeliver As Double
    
    QtyOrdered = DLookup("QtyOrdered", "dbo_v_PurchaseOrderItemStatus", "PurchaseOrderDetailID=" & Me.PurchaseOrderDetailID)
    QtyDelivered = DLookup("QtyDelivered", "dbo_v_PurchaseOrderItemStatus", "PurchaseOrderDetailID=" & Me.PurchaseOrderDetailID)
    
    LeftToDeliver = QtyOrdered - QtyDelivered
    
    If Me.txtQuantity > LeftToDeliver Then
        MsgBox "TOO MUCH"
        Me.Undo
        Cancel = True
    End If
    
End Sub

该代码在新增交货记录时运行正常，但编辑已有交货记录时会出现校验误判：SQL视图统计的累计交货量QtyDelivered包含了当前编辑记录的旧值，计算剩余可交量时没有剔除这部分即将被替换的数值，导致剩余可交量计算偏小。

问题复现 #

以上述示例数据为例：

1. 视图统计基础值：QtyOrdered = 10，QtyDelivered = 8
2. 业务场景：第二条交货记录实际数量应为4，用户编辑该条记录将数量从3改为4
3. 原有代码计算逻辑：LeftToDeliver = 10 - 8 = 2，判断输入值4>2，触发超限提示
4. 实际正确结果：修改后累计交货量为5+4=9，未超过订购量10，校验逻辑错误

修复方案 #

核心修复逻辑：编辑已有记录时，从视图统计的累计交货量中扣除当前记录的旧交货量（该值保存在控件的OldValue属性中，是未保存前的原始数据库值，已被计入QtyDelivered统计结果，编辑后会被新输入值替换），再计算剩余可交量。
修复后完整代码：

Private Sub txtQuantity_BeforeUpdate(Cancel As Integer)
    Dim QtyOrdered As Double, QtyDelivered As Double, LeftToDeliver As Double
    Dim OldQty As Double
    
    ' 读取采购订购量、数据库统计的累计交货量，空值按0处理
    QtyOrdered = Nz(DLookup("QtyOrdered", "dbo_v_PurchaseOrderItemStatus", "PurchaseOrderDetailID=" & Me.PurchaseOrderDetailID), 0)
    QtyDelivered = Nz(DLookup("QtyDelivered", "dbo_v_PurchaseOrderItemStatus", "PurchaseOrderDetailID=" & Me.PurchaseOrderDetailID), 0)
    
    ' 编辑场景下，扣除当前记录的旧值，避免重复统计
    If Not Me.NewRecord Then
        OldQty = Nz(Me.txtQuantity.OldValue, 0)
        QtyDelivered = QtyDelivered - OldQty
    End If
    
    ' 计算实际剩余可交货量
    LeftToDeliver = QtyOrdered - QtyDelivered
    
    ' 校验：交货量不能为负，不能超过剩余可交量
    If Nz(Me.txtQuantity, 0) < 0 Or Nz(Me.txtQuantity, 0) > LeftToDeliver Then
        MsgBox "交货数量不合法，当前采购明细剩余可交货数量为：" & LeftToDeliver, vbExclamation
        Me.Undo
        Cancel = True
    End If
End Sub

修复说明 #

• 修正了原有变量声明的语法问题：VBA中Dim a,b,c As Double写法会将a、b声明为Variant类型，修复后每个变量明确指定为Double类型
• 加入Nz函数处理空值，避免DLookup返回Null时触发类型不匹配错误
• 通过Me.NewRecord区分新增/编辑场景，编辑场景自动扣除当前记录旧值，无需修改后端SQL视图，改动量最小
• 新增交货量非负校验，符合实际业务规则
• 报错提示明确展示剩余可交货量，方便用户录入参考

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