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?