How do you auto generate a number?

[Malkavian]

I would like to create a form that generates a random 6 digit number when creating a new record and then use this number field as the primary key. Only thing is I'm not too good at the coding. Can anyone help me?

- Malk

 

[RuralGuy]

Do you object to using an AutoNumber field? If so, why?

 

[Malkavian]

If I use an autogenerated field option... it creates numbers in excess of 6 digits and sometimes the value is negative.

Id like to generate unique six digit numbers of a positive value.

- Malk

 

[Bat17]

Why random? just use autonumber and format to 6 digits or seed the start at 100000

Peter

 

[Malkavian]

Random because its a check number and im dealing with 30,000+ individual cases.

How do I "seed" or format the field to a positive 6 digit number? And can the randomisation be done?

- Malk

 

[Bat17]

Randomize    ' Initialize random-number generator.
MyField = Int((900000 + 1) * Rnd + 100000)

This will give you your number but you would need to add some sort of checking to prevent duplicates.

Peter

 

[Bat17]

hopefully with only 30,000 records you wont get too many clashes so the loop wont have to keep checking

Dim i As Long
Randomize    ' Initialize random-number generator.
i = Int((900000 + 1) * Rnd + 100000)
Do While DCount("ID", "MyTable", "ID = " & i)
    i = Int((900000) * Rnd + 100000)
Loop
MyField = i

Peter

 

[Malkavian]

Private Sub Serialtxt_BeforeUpdate(Cancel As Integer)

Dim i As Long
Randomize    ' Initialize random-number generator.
i = Int((900000 + 1) * Rnd + 100000)
Do While DCount("ID", "Check Requests", "ID = " & i) ' "Check Requests" is the table name.
    i = Int((900000) * Rnd + 100000)
Loop
SERIAL = i ' SERIAL is the field name listed in the table "Check Requests"

End Sub

I attached this code to a text box on the Form. Everytime I create a new record the value in the textbox returns blank both on the form and in the table.

Help.

- Malk