Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Picking 50 random numbers (A2k)

    Borrowing some of HansV's code I created a function to populate a table with 50 random numbers from another tables autonumber field.
    Problem: The code selects some of the recordID's that have been deleted and I'm getting some duplicates in the new table that I'm populating with the 50 numbers.
    How do I check for only the actual numbers in the autoNumber field and not just the count of them
    Here is the function
    Function Create50()

    Dim intRandom As Integer
    Dim intPreviousRandom As Integer
    Dim CntRecord As Integer
    Dim i As Integer
    Dim db As Database
    Dim rst, rst1 As Recordset

    Set db = CurrentDb

    Set rst = db.OpenRecordset("qryResponders") 'query of the table that has the AutoNumber field
    Set rst1 = db.OpenRecordset("tblID") ' table that I'm appending the random 50 numbers to
    CntRecord = rst.RecordCount

    i = 1
    For i = 1 To 100 - 1
    intPreviousRandom = intRandom
    ' Get random number until different from previous value
    Do
    intRandom = Int(Rnd * CntRecord + 1)
    Loop Until intRandom <> intPreviousRandom

    rst1.AddNew
    rst1!ID = intRandom
    rst1.Update
    i = i + 1
    Next

    End Function

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking 50 random numbers (A2k)

    I would change the code as follow :
    <pre>rst1.AddNew
    rst.MoveFirst
    rst.Move intRandom
    rst1!ID = rts!ID
    rst1.Update
    i = i + 1
    Next
    Set rst = Nothing
    Set rst1 = Nothing</pre>

    Francois

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking 50 random numbers (A2k)

    Francois,
    Thanks
    It is selecting the correct numbers now but I still get some duplicate numbers in the final table
    Any suggestion on that
    Scott

  4. #4
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking 50 random numbers (A2k)

    Duplicates can be avoided if you make the recordID field in tblID a primary key.

    I also found another way of generating random records and populating a table with the results. I lfted it from here The Access Web



    <pre>'************ Code Begin ***********
    'Code courtesy of
    'Joe Foster
    Function Randomizer() As Integer
    Static AlreadyDone As Integer
    If AlreadyDone = False Then Randomize: AlreadyDone = True
    Randomizer = 0
    End Function
    '************ Code End *************



    Public Function RandomPerson()

    Dim strSQL As String
    Dim strSQLDelete


    strSQLDelete = "DELETE * from tblid"


    'Now to get 50 IDs picked at random:
    strSQL = "INSERT INTO tblID ( recordD ) "
    strSQL = strSQL & "select top 50 qryResponders.recordid "
    strSQL = strSQL & "from qryResponders "
    strSQL = strSQL & "where Randomizer() = 0 "
    strSQL = strSQL & "order by rnd(isnull(qryResponders.recordid) * 0 + 1)"


    DoCmd.RunSQL strSQLDelete 'empties table

    DoCmd.RunSQL strSQL 'runs append query

    End Function</pre>



    HTH

    Mark

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking 50 random numbers (A2k)

    Mark,
    Thanks, I forgot to check that resource
    That worked out well.
    Scott

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Picking 50 random numbers (A2k)

    I posted some code a while back that generates a unique random number for each record in a table (or other recordset), from 1 to the total number of records in table. Haven't looked at this in a while, but did a quick test just now & seems to work OK. This sub uses a slightly different method to generate the random numbers. If interested, see this post:

    <!post=Re: Random Numbers (Office XP),181777>Re: Random Numbers (Office XP)<!/post>

    HTH

Posting Permissions

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