How to best accomplish a requirement on Autonumber?

[Adam Clauss]

I'm not sure if there is an easy way to do this, but here is what I want:
I have a database which will assign IDs to entries as they are entered.
These IDs need to be RANDOM numbers, so the easiest solution to me would
seem to be to use an AutoNumber field set to random.
However, this results in negative numbers as well - which is something I
would like to avoid. Basically, I want a field which generates a random
integer >= 1.

I'm not sure if this can be accomplished with an AutoNumber field or not.
Any ideas on where to start on this?

Thanks!

 

[Dirk Goldgar]

I'm not sure if there is an easy way to do this, but here is what I
want: I have a database which will assign IDs to entries as they are
entered. These IDs need to be RANDOM numbers, so the easiest solution
to me would seem to be to use an AutoNumber field set to random.
However, this results in negative numbers as well - which is
something I would like to avoid. Basically, I want a field which
generates a random integer >= 1.

I'm not sure if this can be accomplished with an AutoNumber field or
not. Any ideas on where to start on this?

You can't get a system-assigned autonumber to do this. You could write
your own function to generate a random number that meets your
requirements, along these lines:

'----- start of code -----
Public Function fncRandomLong() As Long

Const conMaxValue As Long = 2147483647
Static blnRandomized As Boolean

If Not blnRandomized Then
Randomize
blnRandomized = True
End If

fncRandomLong = CLng(Rnd() * conMaxValue)

End Function

'----- end of code -----

You would then use code in a form's BeforeInsert event (or BeforeUpdate,
after checking its NewRecord property) to assign the function's result
to the ID field.

Naturally, by exluding negative numbers you're reducing the size of the
domain by half, so there's a greater chance of collision with some
existing ID. It's still not very great, but you'd need to either need
to check in advance or provide a graceful way to recover from the error
that will be raised if you try to save a record with a key that has
already been used.

 

[Adam Clauss]

Ahh... well, the function itself looks like what I need.
To modify it so that duplicates don't become a problem, could I do something
like this (ignore the syntax, I haven't actually thought out the VB code to
do it...):

Public Function fncRandomLong() As Long

Const conMaxValue As Long = 2147483647
Static blnRandomized As Boolean

If Not blnRandomized Then
Randomize
blnRandomized = True
End If

do
fncRandomLong = CLng(Rnd() * conMaxValue)
while (fncRandomLong exists in Database)

Insert blank entry (with fncRandomLong ID) into database to "hold" the
ID so another user/process does not get it.

End Function


The only problem is - this is not being done interactively via Access. Its
being done through ADO on an ASP (vb script) webpage. Can I still call
these functions from there?

 

[Dirk Goldgar]

Ahh... well, the function itself looks like what I need.
To modify it so that duplicates don't become a problem, could I do
something like this (ignore the syntax, I haven't actually thought
out the VB code to do it...):

Public Function fncRandomLong() As Long

Const conMaxValue As Long = 2147483647
Static blnRandomized As Boolean

If Not blnRandomized Then
Randomize
blnRandomized = True
End If

do
fncRandomLong = CLng(Rnd() * conMaxValue)
while (fncRandomLong exists in Database)

Insert blank entry (with fncRandomLong ID) into database to
"hold" the ID so another user/process does not get it.

End Function

I don't think I'd do all that; I think I'd just attempt to add the
finished record, trap the error if it's a duplicate key, and then try
again with a new key. I'd probably try this for some maximum number of
times, and then give up and display a message to the user.

The only problem is - this is not being done interactively via
Access. Its being done through ADO on an ASP (vb script) webpage.
Can I still call these functions from there?

Not if they're defined in your Access database, so this approach won't
work as such. However, you should be able to write the same function,
or something very close to it, in VBScript.