Access VBA实现带姓氏前缀的HISTORY字段自动递增及空缺补位

我有一个名为odonto的Access数据表，包含code（自动递增）、history、surnames、names字段。需求是自动生成HISTORY字段值：提取surnames（姓氏）的首字母，拼接该字母对应的连续编号（例如FLORES对应F001-F004，MENDOZA对应M001），且当记录被删除后，空缺的编号需被重新使用。

我编写了如下VBA代码实现基础赋值，但无法处理删除记录后的空缺补位问题，请求技术帮助：

Private Sub APELLIDO_AfterUpdate()
Dim MyStr
MyStr = Left([APELLIDO], 1)
Me.LETRA = MyStr

If IsNull(Me.HISTORIA) Then
 Me!HISTORIA = ((MyStr) & "0000" & ([Cant] + 1))
Else
    HISTORIA = Me.HISTORIA
End If

Me.Refresh
End Sub

解决方案：实现空缺编号复用逻辑 #

要解决空缺编号复用问题，核心是先查询当前首字母下的所有已用编号，找出最小的未使用编号，而非简单递增最大编号。具体实现如下：

1. 完整修正后的VBA代码

Private Sub surnames_AfterUpdate()
    Dim lastNameInitial As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    Dim usedNumbers As Collection
    Dim nextNum As Integer
    Dim found As Boolean
    
    ' 提取姓氏首字母并统一为大写，避免大小写分组错误
    lastNameInitial = UCase(Left(Me.surnames.Value, 1))
    
    ' 初始化数据库对象与已用编号集合
    Set db = CurrentDb()
    Set usedNumbers = New Collection
    
    ' 查询当前首字母对应的所有已用编号数字
    sql = "SELECT Val(Mid(history, 2)) AS Num FROM odonto WHERE Left(history, 1) = '" & lastNameInitial & "' ORDER BY Num ASC"
    Set rs = db.OpenRecordset(sql)
    
    ' 将已用编号存入集合
    Do While Not rs.EOF
        usedNumbers.Add rs!Num
        rs.MoveNext
    Loop
    rs.Close
    
    ' 寻找最小的空缺编号
    nextNum = 1
    Do
        found = False
        On Error Resume Next ' 利用集合不存在对应Key时的错误判断
        usedNumbers.Item(nextNum)
        If Err.Number = 0 Then
            found = True
            nextNum = nextNum + 1
        End If
        On Error GoTo 0
    Loop While found
    
    ' 仅当HISTORY字段为空时赋值，保留已有值
    If IsNull(Me.history.Value) Then
        Me!history = lastNameInitial & Format(nextNum, "000") ' 可根据需求调整补零位数，如"0000"为4位
    End If
    
    ' 释放对象，避免内存泄漏
    Set rs = Nothing
    Set db = Nothing
    Set usedNumbers = Nothing
    
    Me.Refresh
End Sub

2. 关键说明

• 引用DAO库：在VBA编辑器中，点击「工具」→「引用」，勾选「Microsoft DAO 3.6 Object Library」（或对应版本）。
• 字段名对应：原代码中的APELLIDO需替换为数据表实际的surnames字段名，HISTORIA对应history字段。
• 编号位数调整：Format(nextNum, "000")生成3位补零编号，如需4位则改为"0000"。
• 并发处理：若多人同时操作数据库，可添加事务锁定逻辑避免编号冲突。

内容的提问来源于stack exchange，提问作者Luis Miguel Flores