Picking 50 random numbers (A2k)

Topic

New Reply

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

Reply | Quote

Replies

I would change the code as follow :

rst1.AddNew
rst.MoveFirst
rst.Move intRandom
rst1!ID = rts!ID
rst1.Update
i = i + 1
Next
Set rst = Nothing
Set rst1 = Nothing

Reply | Quote

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

Reply | Quote

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

'************ 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

HTH

Mark

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

'************ 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

HTH

Mark

Reply | Quote

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:

Re: Random Numbers (Office XP)

HTH

Reply | Quote

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:

Re: Random Numbers (Office XP)

HTH

Reply | Quote

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

Reply | Quote