prevent duplicates in random numbers

[MackBlale]

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?

 

[tina]

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]

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]

comments inline.

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

 

[tina]

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

comments inline.

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

first,

do

third,

as

the

code