Random Number ID field 1

[rccline]

One programmer told me he used 8 to 10 digit random numbers for the ID field, rather than an Autonumber. He's said he's never had a duplicate.

How would one do this?

Thanks.

Robert

 

[Rolliee]

using vb. the function rnd() is a random number. Open a test form and put a command button on it. In the code for the command button put:

MsgBox str(rnd())

and see what happens. YOu will get a number from 0 to 1 - different with each click. Now multiply that by 10,000 and you will get a random number between 1 and 10,0000. Use the int() of that number. You may want to seed it with a date function like format(date, "yyyymmdd&quot + 2


rollie@bwsys.net

 

[rccline]

Rollie:

Thanks! Here's what I came up with for the button control:

Option Compare Database
Private Sub CreateTestID_Click()
MsgBox Str(Rnd()) * (10000000)
End Sub

How do I get that number to enter into the ID field? And how do I assure that it will not be updated; as in the autonumber field, I believe that the number entered into the field is there to stay.

Robert

 

[Rolliee]

try me.IDField = int(rnd()*1000000

rollie@bwsys.net

 

[rccline]

This works!

Private Sub CreateTestID_Click()
Me.tblTestID = Int(Rnd() * 1000000)
End Sub

(1) After the data insert, how do I lock the data, or set it to not allow update or change?

(2) I would like the random numbers to display a set number of digits 000123.

(3) Should I declare a recordset for this form? Would that be the entire database or the table?

Thank you.

Robert

 

[Rolliee]

set the lock on that field not allowing it to be changed by anything but your code. This is done in the field's property fielod on the form.

Rollie E

 

[rccline]

Rolliee:

Locking the field code prohibits changes to the field from the keyboard, but pressing the command button triggers the vba code which does change the contents.

I think the vb code needs an if string...
If Me.tblTestID =0 or "", then enter number, If not MsgBox "This record cannot accept a new ID."

I Locked the field and applied the following code which does not work. I get no message box and, pressing the command button keeps changing the ID.

Private Sub CreateTestID_Click()
'MsgBox Str(Rnd()) * (10000000)
'me.IDField = int(rnd()*1000000
If (Me.tblTestID = 0) Or (Me.tblTestID = "&quot Then
MsgBox ("Record ID already exists&quot
Else
Me.tblTestID = Int(Rnd() * 1000000)
End If
End Sub

Robert

 

[Rolliee]

I think null may be getting you. If you check a field, you need a null workaround or an isnull() check. The workaround is nz() which changes a null string to "" and a null number to 0. Try combining both those tests with and

isnull(Me.tblTestID) and add your randnom number only if it is a null value.


rollie@bwsys.net

email me if you have further problems. I just cit the email notification of this thread.

 

[rccline]

Rollie:

Thank you for the excellent suggestions here and elsewhere on this website.

Robert

 

[danvlas]

Sorry to bug in... but what's the guarantee that Rnd will never return a duplicate value????

Not very likely to happen, but not impossible either...


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[oharab]

As I was reading this thread I was coming to the same conclusion as danvlas. There is no guarantee you will have a unique value unless you build in some validation checking. Are you sure you are not getting mixed up with replication IDs which are non-sequential, but also not random?
Just my 2p's worth.

Ben