Tina, it is absolutely necessary that the ContactID numbers be random. There
will never be more than 1000 as the job is limited. I must not be formatting
correctly because I am not getting the display with the leading zeros like I
need.

If I understand correctly, I should create a table with one row of numbers
from 1-9999. You lost me on the additional yes/no field. Where does that go
and how do I make Access use only unassigned numbers.

Thanks
"tina" wrote:

you can't do this at the table level. you can do it in a form, using perhaps
the BeforeInsert event, or the form's BeforeUpdate event. either way, you'll
need code, probably on the form's Error event, to capture the error
generated when a duplicate value cannot be written to a field with a unique
index. then the equation can be used to produce another random number and
again attempt a save, repeating until a save is successful.

this seems like a very awkward way to get these numbers, though. first, do
the numbers *have to be* random? why not just 1, then 2, then 3, etc? (the
leading zeros are meaningless; if the client number field is Number data
type, it can be formatted to show 4 digits without needing to store leading
zeros). second, what happens when you reach client record 10,000? third, as
more and more numbers are assigned, the equation will assign "already used"
numbers more and more often, necessitating more and more repeats in the code
to finally get an unused number. that seems an unnecessary waste of
computing time, unless randomness is absolutely vital. and if that is the
case, i'd probably use a table of 9999 records, one for each number, with an
additional Yes/No field named Assigned. then i'd pull a random number from
only the *unassigned* numbers in the table, not from all of them.

hth


"MackBlale" wrote in message
...
I have designed a database to produce a random number beteween 0000 and
9999
as unique client number. I used =Int((1000-1+1)*Rnd()+1) under default
value
in properties. I chose yes, no duplicates under Indexed. Everything
works
fine, except when a duplicate random number is generated. I need Access
to
recognize that and generate a new number and continue. Any suggestions?