VB代码中Month Value与Year Value无法按月年重置求助

运行以下VB代码时，每次都会执行计数，但Month Value和Year Value无法按月、按年自动重置。需要实现每月重置Month Value、每年重置Year Value的功能，修复方案如下：

原代码 #

Private Sub Proforma_Number_Generator_Command_Click()
    
    Dim vLastM As Variant
    Dim accM As Integer
    Dim vLastY As Variant
    Dim accY As Integer

    'Sets the date of the Proforma Invoice Number to Today'
    'Me.Proforma_Invoice_Date = Format(Date, "yyyy-mm-dd")

    vLastM = DMax("[Month Value]", "[Proforma Invoice Form Table]", _
                  "PI_Month='" & Me.PI_Month.Value & "' AND PI_Year ='" & _
                  Me.PI_Year.Value & "'")

    If IsNull(vLastM) Then
        accM = 1
    Else
        accM = vLastM + 1
    End If

    Me.Month_Value = accM

    'Year'

    vLastY = DMax("[Year Value]", "[Proforma Invoice Form Table]", _
                  "PI_Year='" & Me.PI_Year.Value & "'")

    If IsNull(vLastY) Then
        accY = 1
    Else
        accY = vLastY + 1
    End If

    Me.Year_Value = accY

    Me.Order_No = Format("ON" & "-" & Format(Date, "yyyy") & "-" & Me.Year_Value)

End Sub

修复后的代码 #

Private Sub Proforma_Number_Generator_Command_Click()
    
    Dim currentMonth As Integer
    Dim currentYear As Integer
    Dim vLastM As Variant
    Dim accM As Integer
    Dim vLastY As Variant
    Dim accY As Integer

    ' 获取系统当前的月份和年份，避免依赖手动输入
    currentMonth = Month(Date)
    currentYear = Year(Date)
    
    ' 将当前年月同步到表单控件（如果需要在界面显示）
    Me.PI_Month = currentMonth
    Me.PI_Year = currentYear

    ' 按当前年月筛选，获取当月最大的Month Value
    vLastM = DMax("[Month Value]", "[Proforma Invoice Form Table]", _
                  "PI_Month = " & currentMonth & " AND PI_Year = " & currentYear)

    ' 当月无记录则从1开始，否则累加
    accM = IIf(IsNull(vLastM), 1, vLastM + 1)
    Me.Month_Value = accM

    ' 按当前年份筛选，获取当年最大的Year Value
    vLastY = DMax("[Year Value]", "[Proforma Invoice Form Table]", _
                  "PI_Year = " & currentYear)

    ' 当年无记录则从1开始，否则累加
    accY = IIf(IsNull(vLastY), 1, vLastY + 1)
    Me.Year_Value = accY

    ' 生成格式统一的订单号
    Me.Order_No = "ON-" & Format(currentYear, "0000") & "-" & accY

End Sub

关键修改说明 #

• 自动获取当前年月：用Month(Date)和Year(Date)直接读取系统日期的年月，不再依赖表单控件的手动输入，避免因输入错误导致重置逻辑失效，同时确保年月切换时自动触发计数重置。
• 修正筛选条件语法：如果PI_Month和PI_Year是数字类型字段，移除原代码中的单引号（单引号会导致数字字段匹配失败），确保DMax能正确查询到对应年月的最大计数值。
• 简化计数逻辑：用IIf函数替代冗余的If-Else判断，让代码更简洁紧凑。
• 统一订单号格式：直接使用获取到的currentYear格式化年份，避免和Date函数的重复调用，保证格式一致性。

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