Rnd (random) number duplication (Access XP)

[RioDeSmoke via AccessMonster.com]

I have some code that loops through a Recordset in order to select an X
amount of random records. When I select 10 records I get 10 -- 20 I get 20
and so on. But, when I select 400, I get 369 at times and 381 at others.
The difference gets greater the more records I select. I believe what is
happening is that the random numbers being generated are being duplicated and
it can't select the record more than once. Here is my code within my loop:

MyRS.MoveLast
NumOfRecords = MyRS.RecordCount
Randomize
SpecificRecord = Int((NumOfRecords * Rnd) + 1)

Has anyone ever seen this? Please help if you can this is driving me crazy!

 

[AccessDB.Info]

The more records you are trying to select with a random number, the more
likely you are to select the same record more than once.

Have you tried using the method in this KB article?
http://support.microsoft.com/kb/287682

You could always contruct the SQL string and use it for recordset.
SELECT TOP 400 tblData.*, Rnd(PrimaryKey) AS rndOrder
FROM tblData
ORDER BY Rnd(PrimaryKey);

 

[John Spencer]

You don't say how you are "marking" records as selected. But I would say you
need to check the number selected at the end of your current process and if not
enough then continue adding records with your process until you have the
required number.

 

[AccessDB.Info]

Have you tried using the method in this KB article?
http://support.microsoft.com/kb/287682

Here's some code.

Dim db As DAO.Database, rst As DAO.Recordset
Dim lngNumOfRecords As Long, strSQL As String

lngNumOfRecords = 400
strSQL = "SELECT TOP " & lngNumOfRecords & " tblData.* FROM tblData ORDER
BY Rnd(len(tbldata.strstring));"

Set db = CurrentDb()
Randomize
Set rst = db.OpenRecordset(strSQL)

'VBA Code ....

rst.Close
db.Close

A more difficult method would be to create an array to store your random
numbers, but you would have to check the array before adding a number to make
sure that number doesn't already exists in the array. Then use the array to
select your records.