create module to insert random numbers

[MackBlale]

I have created a database to track employees for a military contract. I need
to create a field for a unique client ID number that is only filled in when I
click a button on the form "Assign Client ID" I want to create a module that
will generate a random number between 1 and 10,000, check the records to make
sure it does not already exist as a client id, and then insert the number
into the current record. If the number does already exist, I want to
continue generating numbers and checking until an unused number is found.
The numbers must be random and the employees will never number more than
10,000.

 

[Scott Lichtenberg]

Look at the Access RND function. To produce random integers in a given
range, use this formula:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

You can set up a While Loop to generate the random number, check it against
your existing table, and regenerate it if necessary. Just as a side note,
this is probably not a good way to do things. If you have relatively few
employees, you should be OK, but as the number of employees grows, you are
going to generate more an more duplicate IDs. By the time you get up to
9000 records, it is going to take a whole lot of randomizations before you
get one that is available.

Another approach would be to set up a table with 10000 prenumbered records.
Generate a random number as above, go to that record in the table, then read
through the records sequentially to find the next unassigned record.

Hope this helps.

 

[MackBlale]

Scott,
Initially I used the RND function on table properties to define the data
with a no randoms allowed. The database would lock up when a random number
was generated. There will probably never be more than 1500 clients, that I
am sure of.
10,000 is the number to make sure I have less of a chance of getting the
same number. I tried the 10000 number in a table method and it is way too
cumbersome for me to work. I need for Access to generate the number for a
single record on demand, make sure it hasn't been assigned, and place it in
the current record. I simply need the code to do it because I really don't
understand the modules or macros in Access.

 

[Jeff Boyce]

You've outlined some very specific constraints on the creation of a unique
row ID.

Why? What will having a "random" unique row ID allow you to do that you
couldn't do otherwise?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MackBlale]

The employees I am dealing with are contracted to gather human intelligence
in Iraq. I must have a unique ID for each individual that is non-sequential
to prevserve the true identity of the person from those who don't need to
know. I have 52 different locations and sequential numbers can be traced
back to a single user. My employee pool is around 500 but will never be more
than 1500. 10,000 random numbers to choose from should be adequate. How do
I do it?
Thanks

 

[Jeff Boyce]

Use your favorite on-line search engine. Enter:
ms access random number generator

You should get a lot of 'hits'. Use the technique that works best in your
situation.

Regards

Jeff Boyce
Microsoft Office/Access MVP