Hi ALL
I need to generate a random number, then check whether that is already used in a table. If used already then continue generating and checking till it reach a unique number which is not used in the table. Hope my question is clear. If anyone could help I would be greatful.
Thank you
The script for a VBA sub
1. dim a the range of the numbers (highest - lowest)
2. randomize
3. generate random number (rn)
4. loop while a(rn)>0
generate random number (rn)
5. a(rn)=1
Thank you rcm
What I am trying is duplicating a record from a form and add to table. The record has a unique number. So when I duplicate I want to give the new record a unique number after checking in the table.
So far I have done like below
But my problem now is after reaching the maximum number the loop continues. how can I exit here and then still duplicate the record and show the record without the unique number, allow the user to enter a number and save. I am using 1 to 10 for test purpose only. But in real case it will be 25000 to 99999
Please Login or Register to view this content.
Hi,
Random without duplicates is a normal problem. Say you want to have random numbers from 25000 to 99999 without any duplicates. Here is how you do the problem!
In A1 to A74999 put your numbers. Then in B1 put this formula "=Rand()" and copy it down to B74999. Sort the two columns by column B.
In Column A will then be random numbers with no duplicates. NO VBA needed!!!.... If you simply recalculate and sort again you get a new random picks...
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Sorry MarvinP. I want to use it in Access table.
Sorry I missed the Access instead of Excel type of question.
So you have a table with a field called ID and it has some random numbers missing in the range from 1 to 1000. You are attacking this problem by generating a number and seeing if it has already been used. You keep checking until you find one that hasn't been used. This seems a hard way to do the problem.
How about doing this problem in another way, using easy access tables queries. Table A is like above and has 3 numbers missing in the range from 1 to 1000. You create a second Table B that has all numbers from 1 to 1000 in it and do a simple "Find Unmatched Query" using the Query Wizard. You would get 3 numbers in this query and simply use one of them... Does this sound like an easier method to find those missing random numbers?
Thank you MarvinP for the reply.
I will explain.
I have a bound form. In certain situation I want to duplicate the current record on the form and add to the table. The table has a unique number field which will not accept duplicate numbers. I need to keep the property same as it is. So while duplicating the record from the form I want to give a number for the duplicated record from the randomized code but it should check the table that whether the generated number is already used earlier or not. If the number is already used and the random code is unable to generate a new number due to the high and low number range, it should exit the procedure and keep the duplicated record in the form as current record with the option of giving a number manually by the user.
Thank you for the support and expecting a solution
Ok - I see where picking a random number and then checking it might take a long time. I think picking a random number in the range is the wrong approach.
I think I'd use a incrementing counter to see if the number is used or not and if it is then try the next larger number. If you really need a random and unused number in the range then you could start at a random place in the range as your first pick. It sounds like you just need to find an unused number.
Something more like:
Please Login or Register to view this content.
Thank you for the code. That approach is good. Only thing is when it finds a number in the table I want to exit the sub and prompt the user to input manually.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks