A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

prevent duplicates in random numbers



 
 
Thread Tools Display Modes
  #1  
Old October 25th, 2008, 07:34 PM posted to microsoft.public.access.tablesdbdesign
MackBlale
external usenet poster
 
Posts: 19
Default prevent duplicates in random numbers

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?
  #2  
Old October 26th, 2008, 12:57 AM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default prevent duplicates in random numbers

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?



  #3  
Old October 26th, 2008, 07:22 AM posted to microsoft.public.access.tablesdbdesign
MackBlale
external usenet poster
 
Posts: 19
Default prevent duplicates in random numbers

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?




  #4  
Old October 26th, 2008, 07:18 PM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default prevent duplicates in random numbers

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?





  #5  
Old October 27th, 2008, 05:43 AM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default prevent duplicates in random numbers

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?







 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.