'Me.xsddid = AutoNumStr("tblxsddzj", "xsddid", 4, "XS", "yyyymm")------XS2011050001
'这自动编号代码复制到模块里,就可以引用AutoNumStr()函数自动编号了
'函数名称: AutoNumStr
'函数描述: 文本型自动编号函数
'输入参数: TableName 表名
' FieldName 字段名
' Digit 顺序号位数
' Prefixal 前缀字符,可选
' Dateformat 日期部分的格式,可选
'返回参数: 调用成功返回新的编号,调用失败返回Null
'调用示例: =AutoNumStr("员工表","工号", 5, "YG") 返回YG00001
' =AutoNumStr("订单表","订单号",3,"XS","yyyymmdd-") 返回 XS20100101-001
'作者:红尘如烟
'===============================================================================================
Public Function AutoNumStr(TableName As String, _
FieldName As String, _
Digit As Integer, _
Optional Prefixal As String, _
Optional DateFormat As String)
On Error GoTo ErrorHandler
Dim strPrefixal As String
Dim strTemp As String
strPrefixal = Prefixal
If DateFormat <> "" Then strPrefixal = strPrefixal & Format(Date, DateFormat)
If strPrefixal <> "" Then strTemp = "[" & FieldName & "] Like '" & strPrefixal & "*'"
strTemp = Nz(DMax(FieldName, TableName, strTemp), "0")
strTemp = Val(Mid(strTemp, Len(strPrefixal) + 1)) + 1
strTemp = Format(strTemp, String(Digit, "0"))
AutoNumStr = strPrefixal & strTemp
Done:
Exit Function
ErrorHandler:
MsgBox "错误编号: #" & Err & vbCrLf & _
"错误来源: " & Err.Source & vbCrLf & _
"错误信息: " & Err.Description, vbCritical, "出错"
Resume Done
End Function
示例
点击下载此附件