Random (1 Viewer)

[Hayvek]

i need to produce a random 4-digit number that cant be the same as any other in that record. now i tried to use the autonumber function in access and put to restrict it to four digits with the use of input masks/formatting but it didnt like it.

am i looking at vb code? or is there another way around it?

thank you in advance.

 

[shay]

Try

Randomize
RandomNo = Int(9999 * Rnd + 1)


The first line initialises the random number generator and the second line gives you a number between 1 and 9999.

hth

shay

 

[Hayvek]

oooo nice one, where do i put that? into a module?

 

[Rakier]

That code will generate the random number that you need, but you will have to do some validation as you do not want it to be the same as any other number in the record. When the random number is produced, you will need to check it against the other numbers in the record, then if it matches one of those other numbers, you will need to regenerate the random number...until you get one that doesn't match it.

 

[Hayvek]

do you have any idea how i would go about that?

 

[Rich]

Are you never going to have 10,000 records?

 

[Hayvek]

nope

 

[shay]

Hayvek

Difficult to say where you should put the code as I don't know what you're trying to do. Hopefully this example will help. I created a form which has table "Random tbl" as recordsource. The table contains integer field "RandomNo" which is the controlsource for text box "txtRandomNo".

Ok. I call the Randomize function when the form's loaded. I've put a button "cmdGenerateRandomNo" on the form and a new number is generated each time it is clicked.

I doubt you'll want to use a button to generate the number but hopefully this will point you in the right direction.

hth

shay

Private Sub cmdGenerateRandomNo_Click()

Dim NewRand As Integer

Do
NewRand = Int(9999 * Rnd + 1)
Loop While (DCount("*", "Random tbl", "[RandomNo]=" & NewRand) > 0)
Me.txtRandomNo = NewRand

End Sub

Private Sub Form_Load()

Randomize

End Sub