Module or Macro or What? Please Help!!!

[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 or
macro 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
1500 persons.

 

[Allen Browne]

The simplest way to do this would be create an Autonumber in your able, and
set its New Values property to Random.

This will give you a random unused signed long integer.

Otherwise you will need to write code using Rnd() (don't forget to
Randomize), multiply by 10k, convert to integer, check if it exists and loop
until you get a non-used one, assign the number. You will also need to write
a custom locking strategey to handle the case where the same number is
assigned to different users/processes at the same time. And you will have
all sorts of fun trying to import data or execute an append query.