How random are randomly generated numbers ?

[Savvoulidis Iordanis]

Hi there. Going straight to the problem. (Access 2000, Jet SP8, WinXP)

I have a table that has a string primary key, consisting of 25 numbers,
which are generated randomly using the following function:
Public Function NUMRandom(lowerbound As Long, upperbound As Long) As Long
NUMRandom = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
End Function

The function above is called from the following function:
Public Function APPCreateCode() As String
Dim s As String
Dim i As Integer

s = ""

For i = 1 To 25
Randomize
s = s & CStr(NUMRandom(0, 9))
Next

APPCreateCode= s
End Function

After a couple of weeks, this code started producing duplicate key values (I
don't know why, but I got an Access message when trying to insert.) After
this point, whenever the user pressed a button to insert a record, he always
got the duplicate key message. He had to click over twenty times, to get the
record inserted with a random primary key (and sometimes he' d give up
trying). So I changed the code a little as follows:
Public Function APPCreateCode() As String
Dim s As String
Dim i As Integer, j As Integer, li_rnd As Integer
Dim ls_temp As String

s = ""

Randomize
li_rnd = NUMRandom(1, 30)

For i = 1 To 25
For j = 1 To li_rnd
Randomize
ls_temp = CStr(NUMRandom(0, 9))
Next

s = s & ls_temp
Next

APPCreateCode= s
End Function
--------------
What I did, was to execute the randomize function a few more times (and also
for a random number of times in the inside loop. Then I gave the application
back to the client, and everything went well, but only for a couple of
weeks. Then the same problem occured again. Still the user got the duplicate
key message. This is an unexpected access behavior to me and I don't know
what I do wrong.
QUESTIONS:

1) How many characters are compared in the primary by Access, before
displaying the duplicate key message? (a) All of them in the key, or (b)
only a few?

2) If (a), then is there any better way of producing this primary key?

3) Is something wrong with the randomize and rnd functions?

TIA
Savvoulidis Iordanis
Greece

 

[Savvoulidis Iordanis]

Forgot to add that the table till now, has only about 3400 records!!

 

[Klatuu]

You really only need to issue the randomize statement once. It is basically
used to set the seed for the rnd function. I would suggest something like
this:

Public Function APPCreateCode() As String
Dim s As String
Dim i As Integer

s = ""
Randomize
For i = 1 To 25
s = s & CStr(Int(Rnd * 10))
Next i

APPCreateCode= s
End Function

Then, to avoid the error and user frustration, I would check the code before
the insert to ensure the number does not already exist in the table. I think
the above function will give you a better spread, but no random number
generation will ever be completely unique:
For example purposes, MyTable will be the name of the table you want to
create the record for and MyKeyField will be the name of the field you want
to put the number in:

Dim strKeyVal as String
Do While True
'Create a string to use for the code
strKeyVal = APPCreateCode
If IsNull(DLookup("[MyKeyField]", "MyTable", [MyKeyField = '" _
& strKeyVal & "'")) Then
Exit Do 'This code is not in the table
End If
'If the DLookup did not return Null, then that string is alread used, try
another
Loop
'At this point strKeyVal can be used to create a new code