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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|