不要用“自动编号”,自己写一个编号生成器:给你一个
'方法:编号生成器 Generate_BH
'功能说明:在指定范围内根据已有编号,生成一个新编号,要求不会影响其他数据
'参数说明:
' Llimit 下限 as long
' Ulimit 上限 as long
' Cnn 数据连接 用于指明数据库连接 as ADODB.Connection
' Table 表 用于指明生成编号的使用表 as string
' Segment 字段 用于指明表中哪个字段需要进行编号生成
' Term 附加条件 as string
'
'返回值说明:返回数据类型为长整型
'权限说明:所有使用者
'作者:
'更改:
'创建时间:20011105
'最后修改日期:20011106Public Function Generate_BH(ByVal Llimit As Long, ByVal Ulimit As Long, ByRef Cnn As ADODB.Connection, ByVal Table As String, ByVal Segment As String, Optional ByVal Term As String = "") As Long
'*************************定义*************************
Dim OP As SysOperate
Dim sCmd As ADODB.Command
Dim Rs As ADODB.Recordset
Dim BH() As Long
Dim TmpI As Long '临时量
Dim TmpX As Long
'*************************定义*************************'**************************初始化获取数据**********************
Set sCmd = New ADODB.Command
Set Rs = New ADODB.Recordset
Cnn.CursorLocation = adUseClient
sCmd.ActiveConnection = Cnn
sCmd.CommandType = adCmdText
If Trim(Term) = "" Then
Select Case Cnn.Provider
Case "Microsoft.Jet.OLEDB.4.0"
sCmd.CommandText = "Select " & Segment & " from " & Table & " order by " & Segment & " ASC"
Case "SQLOLEDB.1"
sCmd.CommandText = "Select " & Segment & " from " & Table & " order by " & Segment & " ASC"
Case Else
sCmd.CommandText = "Select " & Segment & " from " & Table & " order by " & Segment & " ASC"
End Select
Else
Select Case Cnn.Provider
Case "Microsoft.Jet.OLEDB.4.0"
sCmd.CommandText = "Select " & Segment & " from " & Table & " Where ISNUMERIC(" & Segment & ")=true AND " & Term & " order by " & Segment & " ASC"
Case "SQLOLEDB.1"
sCmd.CommandText = "Select " & Segment & " from " & Table & " Where ISNUMERIC(" & Segment & ")=1 AND " & Term & " order by " & Segment & " ASC"
Case Else
sCmd.CommandText = "Select " & Segment & " from " & Table & " Where ISNUMERIC(" & Segment & ")=1 AND " & Term & " order by " & Segment & " ASC"
End Select
End If
Set Rs = sCmd.Execute
'**************************初始化获取数据**********************
'***************************检查错误*******************
If Rs.BOF And Rs.EOF Then '检查数据库
Generate_BH = Llimit
Exit Function
End If
Rs.MoveFirst
If Rs.Fields.Item(Segment).Value < Llimit Then '检查数据库
If MsgBox("数据库无效!退出程序,检查表 " & Table & " 的 " & Segment & " 字段.", vbOKOnly, "编号生成") = vbOK Then
Exit Function
End If
End If
Rs.MoveLast
If Rs.Fields.Item(Segment).Value > Ulimit Then '检查数据库
If MsgBox("数据库无效!退出程序,检查表 " & Table & " 的 " & Segment & " 字段.", vbOKOnly, "编号生成") = vbOK Then
Exit Function
End If
End If
'***************************检查错误******************
'***************************初始化数据********************
Rs.MoveFirst
ReDim BH(Rs.RecordCount - 1) As Long
TmpX = 0
For TmpI = LBound(BH) To UBound(BH) Step 1
BH(TmpI) = Rs.Fields.Item(Segment).Value
Rs.MoveNext
Next
'***************************初始化数据********************
'生成编号
For TmpI = Llimit To Ulimit
For TmpX = LBound(BH) To UBound(BH) Step 1
If TmpI = BH(TmpX) Then
Exit For
End If
If TmpX < UBound(BH) Then
If TmpI > BH(TmpX) And TmpI < BH(TmpX + 1) Then
Generate_BH = TmpI
Set sCmd = Nothing
Set Rs = Nothing
Exit Function
End If
End If
If TmpX = UBound(BH) Then
Generate_BH = TmpI
Set sCmd = Nothing
Set Rs = Nothing
Exit Function
End If
Next
If TmpI = Ulimit Then MsgBox "表" & Table & "的" & Segment & "已经满!"
Generate_BH = -1 '返回-1 当超出范围时!
Next
Set sCmd = Nothing
Set Rs = Nothing
End Function
'方法:编号生成器 Generate_BH
'功能说明:在指定范围内根据已有编号,生成一个新编号,要求不会影响其他数据
'参数说明:
' Llimit 下限 as long
' Ulimit 上限 as long
' Cnn 数据连接 用于指明数据库连接 as ADODB.Connection
' Table 表 用于指明生成编号的使用表 as string
' Segment 字段 用于指明表中哪个字段需要进行编号生成
' Term 附加条件 as string
'
'返回值说明:返回数据类型为长整型
'权限说明:所有使用者
'作者:
'更改:
'创建时间:20011105
'最后修改日期:20011106Public Function Generate_BH(ByVal Llimit As Long, ByVal Ulimit As Long, ByRef Cnn As ADODB.Connection, ByVal Table As String, ByVal Segment As String, Optional ByVal Term As String = "") As Long
'*************************定义*************************
Dim OP As SysOperate
Dim sCmd As ADODB.Command
Dim Rs As ADODB.Recordset
Dim BH() As Long
Dim TmpI As Long '临时量
Dim TmpX As Long
'*************************定义*************************'**************************初始化获取数据**********************
Set sCmd = New ADODB.Command
Set Rs = New ADODB.Recordset
Cnn.CursorLocation = adUseClient
sCmd.ActiveConnection = Cnn
sCmd.CommandType = adCmdText
If Trim(Term) = "" Then
Select Case Cnn.Provider
Case "Microsoft.Jet.OLEDB.4.0"
sCmd.CommandText = "Select " & Segment & " from " & Table & " order by " & Segment & " ASC"
Case "SQLOLEDB.1"
sCmd.CommandText = "Select " & Segment & " from " & Table & " order by " & Segment & " ASC"
Case Else
sCmd.CommandText = "Select " & Segment & " from " & Table & " order by " & Segment & " ASC"
End Select
Else
Select Case Cnn.Provider
Case "Microsoft.Jet.OLEDB.4.0"
sCmd.CommandText = "Select " & Segment & " from " & Table & " Where ISNUMERIC(" & Segment & ")=true AND " & Term & " order by " & Segment & " ASC"
Case "SQLOLEDB.1"
sCmd.CommandText = "Select " & Segment & " from " & Table & " Where ISNUMERIC(" & Segment & ")=1 AND " & Term & " order by " & Segment & " ASC"
Case Else
sCmd.CommandText = "Select " & Segment & " from " & Table & " Where ISNUMERIC(" & Segment & ")=1 AND " & Term & " order by " & Segment & " ASC"
End Select
End If
Set Rs = sCmd.Execute
'**************************初始化获取数据**********************
'***************************检查错误*******************
If Rs.BOF And Rs.EOF Then '检查数据库
Generate_BH = Llimit
Exit Function
End If
Rs.MoveFirst
If Rs.Fields.Item(Segment).Value < Llimit Then '检查数据库
If MsgBox("数据库无效!退出程序,检查表 " & Table & " 的 " & Segment & " 字段.", vbOKOnly, "编号生成") = vbOK Then
Exit Function
End If
End If
Rs.MoveLast
If Rs.Fields.Item(Segment).Value > Ulimit Then '检查数据库
If MsgBox("数据库无效!退出程序,检查表 " & Table & " 的 " & Segment & " 字段.", vbOKOnly, "编号生成") = vbOK Then
Exit Function
End If
End If
'***************************检查错误******************
'***************************初始化数据********************
Rs.MoveFirst
ReDim BH(Rs.RecordCount - 1) As Long
TmpX = 0
For TmpI = LBound(BH) To UBound(BH) Step 1
BH(TmpI) = Rs.Fields.Item(Segment).Value
Rs.MoveNext
Next
'***************************初始化数据********************
'生成编号
For TmpI = Llimit To Ulimit
For TmpX = LBound(BH) To UBound(BH) Step 1
If TmpI = BH(TmpX) Then
Exit For
End If
If TmpX < UBound(BH) Then
If TmpI > BH(TmpX) And TmpI < BH(TmpX + 1) Then
Generate_BH = TmpI
Set sCmd = Nothing
Set Rs = Nothing
Exit Function
End If
End If
If TmpX = UBound(BH) Then
Generate_BH = TmpI
Set sCmd = Nothing
Set Rs = Nothing
Exit Function
End If
Next
If TmpI = Ulimit Then MsgBox "表" & Table & "的" & Segment & "已经满!"
Generate_BH = -1 '返回-1 当超出范围时!
Next
Set sCmd = Nothing
Set Rs = Nothing
End Function
我把你写的那些copy到哪里??你在说的详细些好吗?