自定义自动编号,带断号检测补号功能
时 间:2008-11-06 09:17:04
作 者:andymark ID:1350 城市:深圳
摘 要:自定义自动编号,带断号检测补号功能
正 文:
适合格式: XXX-YYMMDD-00000
Public Function ZDYBH(StrTable As String, StrField As String, StrBH As String, Optional FDay As String = "YYMMDD", Optional ILen As Integer = 3, Optional B As Boolean = False) As String
'==================================================================
'功能: 自定义用户编号,带断号检测功能
'格式: "XXX"-"日期格式 "-"0000"
' StrTable 为任意表名称
' StrField 为任意字段名称
' StrBH 编码类型 , 可自义任意格式
' FDay 日期格式 默认日期格式为:YYMMDD",其他符合FORMAT格式的如 YYYYMM,YYYY,YYYYMMDD
' ILen 为数字格式化长度 , 默认长度为3 代表 000 格式
' B 为是否要检测断号 ,默认为FALSE 不检测
'用法: ZDYBH("产量表", "产量ID", "LDH")
' ZDYBH("产量表", "产量ID", Me.Combo1)
' ZDYBH("产量表", "产量ID", Me.Combo1, "YYYY")
' ZDYBH("产量表", "产量ID", Me.Combo1, , 4, True)
'作者: andymark
' QQ : 42503577 , ewang11@163.com
'日期: 2008-4-2
'===============================================================
Dim Rs As New ADODB.Recordset
Dim Conn As New ADODB.Connection
Dim Str As String
Dim Num As Integer
Dim TNum As Integer
Dim StrWhere As String
Dim StrOrderWhere As String
Dim StrOrderWhereDesc As String
Set Conn = CurrentProject.Connection
Str = StrBH & "-" & Format(Now(), "" & FDay & "") & "-"
StrWhere = "select " & StrField & " from " & StrTable & " where " & StrField & " like '" & Str & "%'"
StrOrderWhere = StrWhere & " Order by " & StrField & ""
StrOrderWhereDesc = StrWhere & " Order by " & StrField & " DESC"
Rs.Open StrOrderWhereDesc, Conn, adOpenKeyset, adLockOptimistic
If Rs.EOF Then
Num = 0
Else
Num = Right(Rs.Fields("" & StrField & ""), ILen)
If B = True Then
If CInt(Num) <> Rs.Fields.Count Then
Rs.Close
Rs.Open StrOrderWhere, Conn, adOpenKeyset, adLockOptimistic
Num = 0
Do While Not Rs.EOF
TNum = CInt(Right(Rs.Fields("" & StrField & ""), ILen))
If TNum = Num Then
MsgBox StrBH & "-" & Format(Now(), "" & FDay & "") & "-" & Format(Num, String(ILen, "0")) & "存在重号,请检查数据的"
Exit Function
Else
If TNum - Num = 1 Then
Num = TNum
Else
Exit Do
End If
End If
Rs.MoveNext
Loop
End If
End If
End If
ZDYBH = StrBH & "-" & Format(Now(), "" & FDay & "") & "-" & Format(Num + 1, String(ILen, "0"))
Set Rs = Nothing
Set Conn = Nothing
End Function
Access软件网官方交流QQ群 (群号:54525238) Access源码网店
常见问答:
技术分类:
源码示例
- 【源码QQ群号19834647...(12.17)
- 【Access选项卡示例】Ac...(09.09)
- 【Access源码示例】按输入...(09.02)
- 【Access日期区间段查询】...(08.29)
- 【Access日期区间段查询】...(08.27)
- Access怎样才能实现日期时...(08.21)
- 【Access定时打开查询】A...(08.19)
- Access生成固定数量的记录...(08.13)
- Access怎样才能实现日期时...(08.12)
- Access利用导航窗体控件对...(08.03)
学习心得
最新文章
- Access表中的字段名、字段标题...(09.19)
- Access快速开发平台--更改“...(09.18)
- 【中秋及国庆优惠】Access培训...(09.15)
- Access如何将日期型的数值转换...(09.14)
- 英文输入法输入数据中存在单引号引起...(09.11)
- 【Access选项卡示例】Acce...(09.09)
- 让Access光标停留在指定的控件...(09.07)
- 关于Access查询条件里使用通配...(09.06)
- Access报表偷懒制作法--Ac...(09.05)
- Access快速开发平台--窗体数...(09.04)