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.

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" <(E-Mail Removed)> wrote in message
news:B5099FAC-F9BA-4F44-A48E-(E-Mail Removed)...
>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,
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.

"Scott Lichtenberg" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:B5099FAC-F9BA-4F44-A48E-(E-Mail Removed)...
> >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.
>
>

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" <(E-Mail Removed)> wrote in message
news:3E6F547E-E556-416E-807E-(E-Mail Removed)...
> 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.
>
> "Scott Lichtenberg" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:B5099FAC-F9BA-4F44-A48E-(E-Mail Removed)...
>> >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.
>>
>>

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:3E6F547E-E556-416E-807E-(E-Mail Removed)...
> > 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.
> >
> > "Scott Lichtenberg" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:B5099FAC-F9BA-4F44-A48E-(E-Mail Removed)...
> >> >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.
> >>
> >>
>
>
>

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




"MackBlale" <(E-Mail Removed)> wrote in message
news091B3BF-FCCF-4589-AD63-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:3E6F547E-E556-416E-807E-(E-Mail Removed)...
>> > 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.
>> >
>> > "Scott Lichtenberg" wrote:
>> >
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:B5099FAC-F9BA-4F44-A48E-(E-Mail Removed)...
>> >> >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.
>> >>
>> >>
>>
>>
>>