94说,计算出ID后,在保存前,要在数据库查询一下是否有重复的ID。因为RND函数不能保证每次生成的伪随机数不一致。 用SQL就可以了。 select * from tablename where filed name ="id"
看一下结果的recordcount是否为0就行。
看一下结果的recordcount是否为0 前最好Movelast 一下
dim strTemp as stringset rs = cnn.execute("select * from yourtable where id>='20021203000' and id<='2002120366'") rs.movelast if rs.absoluteposition = 67 then msgbox "编号已满" exit sub end ifrandomize do strtemp = "20021203" & format(rnd*67,"00#") set rs = cnn.execute("select * from yourtable where id='" & strtemp & "'" if rs.eof then exit do loop 'strtemp就是所需编号 ......
Public Sub CreateRandomArray(ArrayName() As Long, Min As Long, _ Max As Long, Duplicable As Boolean, BackProcess As Boolean) '****************************************************************** ' 名 称:随机数组生成函数 ' 作 用:生成随机数组(有或无重复) ' 参数表:ArrayName() As Long 目标数组 ' Min As Long 最小值 ' Max As Long 最大值 ' Duplicable As Boolean 可否重复 ' BackProcess As Boolean 是否后台生成,会影响速度 ' 返回值:ArrayName() As Long ' 说 明:仅限一维长整型数组,数组编号从 1 开始。当生成无重复连续 ' 数组时(如生成一个含有1000个元素且数的范围为1—1000), ' 速度会大幅度增加 '****************************************************************** Dim i As Long If Min >= Max Then MsgBox "Min 必须小于 Max", vbOkOnly + vbExclamation, "错误" Exit Function End if Randomize Timer If Duplicable = True Then For i = 1 To UBound(ArrayName()) If BackProcess Then DoEvents ArrayName(i) = Int((Rnd * (Max - Min + 1) + Min)) Next i Else If Max - Min + 1 = UBound(ArrayName()) Then For i = 1 To UBound(ArrayName()) If BackProcess Then DoEvents ii = Int(Rnd * (UBound(ArrayName())) + 1) If ii <> i Then Swap ArrayName(i), ArrayName(ii) Next i Else For i = 1 To UBound(ArrayName()) Do ArrayName(i) = Int((Rnd * (Max - Min + 1) + Min)) Loop Until IfDuplicate(ArrayName(), i, BackProcess) = False Next i End If End If End Function用以上函数,代码如下 : Dim a(1 to 67) as long Call CreateRandomArray(a(),1,67,False,False) 执行完后a(1)到a(67)中存放的就是按乱序排列的从1到67这67个数 然后用它生成字串就行了: "030" & Format(a(i)-1,"00")
以上代码中要用到一个Swap函数,该函数如下: Sub Swap(a as Variant, b as Variant) dim c as Variant c=a a=b b=c End Sub
用SQL就可以了。
select * from tablename where filed name ="id"
rs.movelast
if rs.absoluteposition = 67 then
msgbox "编号已满"
exit sub
end ifrandomize
do
strtemp = "20021203" & format(rnd*67,"00#")
set rs = cnn.execute("select * from yourtable where id='" & strtemp & "'"
if rs.eof then exit do
loop
'strtemp就是所需编号
......
Max As Long, Duplicable As Boolean, BackProcess As Boolean)
'******************************************************************
' 名 称:随机数组生成函数
' 作 用:生成随机数组(有或无重复)
' 参数表:ArrayName() As Long 目标数组
' Min As Long 最小值
' Max As Long 最大值
' Duplicable As Boolean 可否重复
' BackProcess As Boolean 是否后台生成,会影响速度
' 返回值:ArrayName() As Long
' 说 明:仅限一维长整型数组,数组编号从 1 开始。当生成无重复连续
' 数组时(如生成一个含有1000个元素且数的范围为1—1000),
' 速度会大幅度增加
'****************************************************************** Dim i As Long
If Min >= Max Then
MsgBox "Min 必须小于 Max", vbOkOnly + vbExclamation, "错误"
Exit Function
End if
Randomize Timer
If Duplicable = True Then
For i = 1 To UBound(ArrayName())
If BackProcess Then DoEvents
ArrayName(i) = Int((Rnd * (Max - Min + 1) + Min))
Next i
Else
If Max - Min + 1 = UBound(ArrayName()) Then
For i = 1 To UBound(ArrayName())
If BackProcess Then DoEvents
ii = Int(Rnd * (UBound(ArrayName())) + 1)
If ii <> i Then Swap ArrayName(i), ArrayName(ii)
Next i
Else
For i = 1 To UBound(ArrayName())
Do
ArrayName(i) = Int((Rnd * (Max - Min + 1) + Min))
Loop Until IfDuplicate(ArrayName(), i, BackProcess) = False
Next i
End If
End If
End Function用以上函数,代码如下 :
Dim a(1 to 67) as long
Call CreateRandomArray(a(),1,67,False,False)
执行完后a(1)到a(67)中存放的就是按乱序排列的从1到67这67个数
然后用它生成字串就行了: "030" & Format(a(i)-1,"00")
Sub Swap(a as Variant, b as Variant)
dim c as Variant
c=a
a=b
b=c
End Sub