Results 1 to 14 of 14

Thread: random number

  1. #1
    Join Date
    Jun 2006
    Posts
    66

    Unanswered: random number

    How can I make a field that defaults to a randome 7 digit number?

  2. #2
    Join Date
    Feb 2004
    Location
    Swindon, UK
    Posts
    86
    Try this.

    Private Sub Form_Current()

    Randomize ' Initialize random-number generator.

    Textbox.Value = Int((9999999 * Rnd) + 1000000) ' Generate random value between 1000000 and 9999999.

    End Sub
    "Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done."

  3. #3
    Join Date
    Jun 2006
    Posts
    66
    where do I put this.. I'd really like it to be the default value of that field.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You can either put this

    =Int((9000000*Rnd())+1000000)

    in the default value of a TextBox in design view if it is bound to the field
    OR
    in the default value of the field in the table design view


    MTB

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    would you application have a problem if two or more records had the same random number?

    MTB's solution will give you what you want, but it begs the question why?
    what is it in your applciation that is requiring a random number.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2006
    Posts
    66
    anyway to do the same but make sure it is unique -- to check that it doesn't exist in the db? can i do this in the table or do I have to do it in the form?

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Access 2003 let's your "AutoNumber" field be random and you can set the format to 7 digits. This guarentees no duplicates, but I don't know how well "mixed" the numbers will be.

    The random function itself doesn't actually generate random numbers as "random" numbers are predetermined. You can simulate "random" by inserting Randomize(Timer) in the code - then you would have to run the command at the exact same time (in milliseconds) to get the same list. To manually assign random numbers and ensure no duplicates requires a loop:

    Generate Number
    Verify it's unique
    Loop if it is not

    As the number of values grows, so will the time to get a unique value.

    good luck,
    tc

  8. #8
    Join Date
    Jun 2006
    Posts
    66
    how would I edit this

    Private Sub Form_Current()

    Randomize ' Initialize random-number generator.

    Textbox.Value = Int((9999999 * Rnd) + 1000000) ' Generate random value between 1000000 and 9999999.

    End Sub


    to make sure the number doesn't already exist in the table?

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    The structure would be along the lines of:
    Code:
    Dim blGood As Boolean
    Dim iR As Integer
    
    blGood = False
    
    Do
    
        Randomize(Timer) ' Timer is the number of seconds from midnight out tw0 decimal places
        iR = Int((9999999 * Rnd) + 1000000)
    
        If DCount("Some Field","Your Table","TheNumber = " & iR) = 0 Then blGood = True
    
    Loop Until blGood
    have fun,
    tc

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    tcace's code will eliminate duplicate entries, but I still think
    iR=(Int(9000000*Rnd())+1000000) is required to limit the range to between
    1000000 and 9999999 ???

    ie max value of Int(9000000*Rnd()) is 8999999 ?? (+ 1000000) = 9999999)


    MTB

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Some nice answers however Access is really not the ideal RDBMS for this sort of thing. To echo Mark\ Mary - what is your (business) requirement for requiring a 7 digit random number? In particular are there any constraints to this number (i.e. if there is a unique constriaint then you aren't actually after a truly random number).

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    What's an RDBMS?

    Mike, you are correct - I simply copied the given code to demonstrate the "no duplicate" loop.

    As given in the help file: RND gives you a Single that is >= 0 and < 1.
    Newer versions of VB appearently eliminate the need for the Randomize function since the "seed" is built in (providing the number given is greater than 1). For what happens when a number between and including 0 and 1 is used, see the help file.

    The given expression for integers in a given range is:
    Code:
    Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
    For your 7 digit number, it's:
    Code:
    Int((9000000) * Rnd + 1000000)
    The largest number Rnd will give you is .999999999 (and a lot more 9's) and the smallest is 0. Move the decimal place 7 times, add the minimum and drop the decimal: 1000000 to 9999999

    The "seed" is:
    An initial value used to generate pseudorandom numbers. For example, the Randomize statement creates a seed number used by the Rnd function to create unique pseudorandom number sequences.
    per Access help file, so I would use this code:
    Code:
    Int((9000000) * Rnd(Timer+1.1) + 1000000)
    (the +1.1 is just in case you run it within 1 second of midnight )

    Coming back to pootle's question: is Access right for you?
    tc

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So you have an answer as to how generate a random number for use within a table, the way to make it unique is give that column a unique index and define the column as non null

    when you attempt to write the record you need to trap for a duplicate error, and if trapped regenerate a new random value and try again

    you will need to ensure that a user cannot change your random number - realtively easy to do on a form, but impossible to do if you allow table edits

    so you have some form tweaking to do

    you need to work out how you will cater for users doing a 'cut and paste' record insert (catches out many designs)

    you need to have a good handle on how the access event model works

    But it would be a lot easier if you could let slip why you need a random number. on the face of it its a fairly unusual requirement
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tcace
    What's an RDBMS?
    Relational Database Management System. Ones that support triggers are much better suited to this sort of thing. There's nothing at all wrong with your answer (as you know) it is just easily circumvented by the user\ overlooked by the developer in Access.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •