=Int((999999-100000+1)*Rnd()+100000)

[louisa]

Hi Everyone,

I have =Int((999999-100000+1)*Rnd()+100000) as my control source so i can get a random number. Unfortunately everytime i reopen my db the random number changes. Is there a way that once that record has been given the random number that it saves it to that record?

 

[vbaInet]

I'm guessing this is related to your other question about a unique Customer Ref number?

If you're going to generate a random number everytime a new record is made, then you want to do it in the Current event of the form instead of the Control Source. Then simply set the value of the field to the newly generated random number

If Me.NewRecord Then
    ... generate random number and set value to the field or control here ...
End If

 

[the_net_2.0]

and don't forget to check for dups using a dcount or something!

very rare with the randomizer in access, but I've seen it happen!

 

[Guus2005]

furthermore, why not simplify the formula to a 900000*Rnd() ?
The Rnd() function returns a number between 0 and 1. In your case the first 100000 numbers are already used. So the formula is 900000*Rnd()+100000

When you put the code in the OnCurrent event, start the Randomizer using the Randomize statement, thus starting the randomizer before using the Rnd() function.

Enjoy!

 

[gemma-the-husky]

if you want repeatable random nubers, though, use rnd WITH the same argument the first time.

 

[louisa]

Hi everyone,

thanks for all your comments and suggestions, as you are probably aware i am learning this as i go and only figured out how to get random numbers generates by looking through the internet. I want a six figure random number hence why i used =Int((999999-100000+1)*Rnd()+100000) I have been looking around online and still cannot get the random number to stay the same once it has been assigned to that record. Any ideas where i could be going wrong?

 

[gemma-the-husky]

you cant have this in the control source, i think, is the problem.


have the form bound to a random number field

then you just need it in the forms before update event

if me.newrecord then
randomnumberfield = yourrandomnumbergenerator
end if

 

[louisa]

Hi,

I have entered the following into the before update event but it doesnt generate a number, anyone know what i am doing wrong?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then RandomNumber = Int((999999 - 100000 + 1) * Rnd() + 100000)
End If

End Sub

 

[DCrake]

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim rNum as long
rNum = Int((999999 - 100000 + 1) * Rnd() + 100000)
If Me.NewRecord Then 
       RandomNumber = rNum 
End If

End Sub

 

[louisa]

I must be doing something completely wrong, i have entered your code and although it doesnt issue an error is doesnt get me a random number either, my field is blank.

 

[DCrake]

Have you tried putting a breakpoint on the code and steppping through it?

 

[louisa]

No because i dont know anything about code so wouldnt have the first clue of how to work through it. I have attached a dummy db with the code in, i have been trying to get it to work in this before i tried in my other db.

 

[DCrake]

Replace the existing code with this amendment

Dim rNum As Long
rNum = Int((999999 - 100000 + 1) * Rnd() + 100000)
If Me.NewRecord Or Me.TestBox = 0 Then
       [TestBox] = rNum
End If

Add a new record move away to the previous record then return to the new record.

I have added the extra code to fill any records that do not have a reandom number in them.

 

[louisa]

If i enter that into the test db it works however if i then put it into my proper db it doesnt, i changed the names, my db has tabs in it so would that stop it from working?
I entered

Dim rNum As Long
rNum = Int((999999 - 100000 + 1) * Rnd() + 100000)
If Me.NewRecord Or Me.RandomNumber = 0 Then
[RandomNumber] = rNum
End If

 

[louisa]

Apologies, i have it working now. Many thanks for your help.

 

[DCrake]

So we know the code is syntactically correct, now we need to find out why it is not working in the live database.

So when you say it doesn't what doesn't?
Is there something else preventing it from running the code.

Here's what to do
Go to the event and click on the extreme left of the code window in the grey area this will produce a round brown dot and a background on the selected line the same colour. This is known as a break point.

Save your form and try and add a new record. When the before update fires it will jump into the code window and the breakpoint line will be in yellow.
Press the F8 function key and watch the yellow line advance one line at a time.

Is it doing as instructed?