one correction here. in tblNumbers, i recommended setting the Field Size
property of field Num to Long Integer. i should have said Integer, instead.
no point using 4 bytes of storage space per entry when 2 bytes will do.

hth


"tina" wrote in message
...
comments inline.

"MackBlale" wrote in message
news
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.

at the table level, make sure the field's Data Type is Number, then set
the
field's Format property to 0000.


If I understand correctly, I should create a table with one row of
numbers
from 1-9999.

re-read my post, hon. i said 9999 *records*, NOT 9999 fields. you want
*one*
number field, data type Number, and do not name the field "number". name
it
something else, like "Num", for instance.

You lost me on the additional yes/no field. Where does that go

it goes next to the Num field. so you have two fields in the table:

Num (data type Number)
Assigned (data type Yes/No)

and how do I make Access use only unassigned numbers.

open a standard module in Access, you can name it modUtilities, and paste
in
the following code, as

Public Function isRandomNum(ByVal X As String) As Single

' the x variable is included to force a call to this function on every
' record in the query. it is NOT used within the function itself.

Randomize
isRandomNum = Rnd

End Function

next, create the numbers table, as

tblNumbers
Num (data type Number, field size Long Integer, primary key)
Assigned (data type Yes/No)
enter 9999 records in the table, as
1
2
3
4
5
etc.

next, create a query to pull a random unused number, as

SELECT TOP 1 Num, Selected FROM tblNumbers WHERE Selected = False ORDER BY
isRandomNum([Num]);

if you're assigning the random number to a field in your data table, using
an open form bound to the data table, there are several ways to get the
number from the above query and then change the Selected field from No to
Yes (False to True) and save the Num record in tblNumbers so that number
isn't used again. the easiest way might be to just bind the above query to
another form, then open that form using code, set the Selected field of
the
returned record to Yes (True), and save the record, then assign the value
of
the Num field to the client number field in your data entry form. assuming
that your data entry form is called frmClients, and the client number
field
is called ClientNum, and the second bound form is called frmNum, the code
would be something like

DoCmd.OpenForm "frmNum", , , , , acHidden
With Forms("frmNum")
!Selected = True
.Dirty = False
End With

Me!ClientNum = Forms("frmNum")!Num

DoCmd.Close acForm, "frmNum", acSaveNo

where you run the above code depends on when and how you want to assign
the
client number. you could call it from the form's BeforeInsert event, or
from
the BeforeUpdate event procedure (though if from there, you need to add
code
to only assign the new random number on a record where there is no
existing
value in the ClientNum field - otherwise you'll be overwriting previously
assigned client numbers), or from a command button on frmClients.

using a hidden form to get an unused random number in a multi-user
environment is questionable, since it will be slower than
opening/updating/closing a recordset entirely in VBA. but if adding new
clients is not a constant, second-to-second action, or if the database is
single-user, it should be okay.

hth


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?