Make ID field a random number

[harrytgs]

Hi,

On an Access database, I would like an ID number to be generated randomly.

This ID number needs to be within the range of 11000 - 99999.

How is this possible?

Many thanks

[Compufreak]

http://www.fontstuff.com/vba/vbatut06.htm

check this out

[harrytgs]

Thanks - will look at now

[harrytgs]

How exactly would this work though? This database's sole duty will (at the beginning) be to generate random numbers and store them (perhaps when a box is ticked).

These are unique numbers for our stock and we have gone up to 11000 so can't go under that, and each number needs to be unique ...

[harrytgs]

Anyone able to help ??

[RuralGuy]

What part of the suggested link did you not understand? It looks like a good solution for you.

[harrytgs]

I will look into this later - I am assuming I will just have to create a macro and then add the function on Access?

[John_G]

Hi -

It's easy enough to generate a random integer in the range you specified:

int(rnd*(99999-11000) + 11000) (or int(rnd*(88999) + 11000) ) will do it for you. Be sure to use type long; if 11000 is not an allowable value, you will need to check for the (rare) occasion that you get it:

Dim MyRandomInt as long
MyRandomInt = int(rnd*(99999-11000) + 11000)
if MyRandomInt = 11000 then MyRandomInt = 11001

That done, you need to check that MyRandomInt is not a duplicate; DLookup is probably the easiest way, and you could put the whole thing in a loop, in a function, like this:

Public Function NextInt() as Long
Dim MyRandomInt as long
do
MyRandomInt = int(rnd*(99999-11000) + 11000)
if MyRandomInt = 11000 then MyRandomInt = 11001
until DCount("*","MyTable", "FieldName = " & MyRandomInt ) = 0
NextInt = MyRandomInt
End Function

Replace MyTable and FieldName with the names of your table and field containing the random numbers.

HTH

John

[harrytgs]

Hi John, I will try this out when I get to the office at 10 today (it's 6 now)

That function sounds perfect for my requirement - although I'm not sure how the function would integrate with my field - would I just have to add =myrandonint in the field's expression?

[harrytgs]

Hi John,
I am adding it on the Visual Basic editor but it doesn't like the line that begins with until...
It just says "Expected: line number or label or statement or end of statement".

Any idea why?

Cheers

[John_G]

Hi -

Oops - sorry! The until line should read

Loop until DCount("*","MyTable", "FieldName = " & MyRandomInt ) = 0

When do you need to generate the random number, i.e. what are doing at the time? My guess would be when you create a new record?

John

[harrytgs]

Hi John,

Yes - it should.

I think the best way is for a form to appear with two fields - one for the new ID and one tick (Yes/No) box, which would generate a new one ...

Is this a good idea?

Cheers

[harrytgs]

I've set the code to the below as a new module (ID Generator):

Public Function NextInt() As Long
Dim MyRandomInt As Long
Do
MyRandomInt = Int(Rnd * (99999 - 11000) + 11000)
If MyRandomInt = 11000 Then MyRandomInt = 11001
Loop Until DCount("*", "Numbers", "ID = " & MyRandomInt) = 0
NextInt = MyRandomInt
End Function


How is this now added on my database - will be playing around and will repost if I work it out!

Many thanks

[harrytgs]

Got it! I made it the default value and it works perfectly

Many thanks