Random Function Problem

[Chris]

I have the below code in a module that I am calling from a
Sub and I am trying to pull back a random list of 5
records for each employee. When I run it I get the first
record for that employee Five time. The same record. I
need to retrieve five different records at random for each
employee.

Any help is appreciated.

-Chris

Sub RandomList4()

Dim D As Database, R As Recordset, T As Recordset
Dim Top, Bottom, K
Dim strRecordID As String
Set D = CurrentDb
Set R = D.OpenRecordset("Select * from
[tblLoans_Reviewed] Where [Application_User_Name]='" &
strcboValue & "';", dbOpenSnapshot)
Set T = D.OpenRecordset("tblLoans")
DoCmd.SetWarnings False
DoCmd.OpenQuery ("qryDeleteTblLoans")
DoCmd.SetWarnings True

Top = 0
Bottom = R.RecordCount - 1
Randomize
For K = 1 To 5
R.MoveFirst
R.Move Int((Top - Bottom + 1) * Rnd + Bottom)
strRecordID = R![Loan_Record_ID]
T.AddNew
T![Loan_Record_ID] = strRecordID
T.Update
Next
End Sub

 

[Allen Browne]

Assuming a numeric primary key named "ID", try this:

Set R = D.OpenRecordset("SELECT TOP 5 * FROM [tblLoans_Reviewed] Where
[Application_User_Name]='" & strcboValue & "' ORDER BY Rnd([ID]);"

The Rnd() function does nothing with the numeric field, but if you don't
pass something in, the optimiser is too clever to call the function every
line.

Presumably you have a Randomize somewhere.

 

[Matthias Klaey]

I have the below code in a module that I am calling from a
Sub and I am trying to pull back a random list of 5
records for each employee. When I run it I get the first
record for that employee Five time. The same record. I
need to retrieve five different records at random for each
employee.

Any help is appreciated.

-Chris

Sub RandomList4()

Dim D As Database, R As Recordset, T As Recordset
Dim Top, Bottom, K
Dim strRecordID As String
Set D = CurrentDb
Set R = D.OpenRecordset("Select * from
[tblLoans_Reviewed] Where [Application_User_Name]='" &
strcboValue & "';", dbOpenSnapshot)
Set T = D.OpenRecordset("tblLoans")
DoCmd.SetWarnings False
DoCmd.OpenQuery ("qryDeleteTblLoans")
DoCmd.SetWarnings True

Top = 0
Bottom = R.RecordCount - 1
Randomize
For K = 1 To 5
R.MoveFirst
R.Move Int((Top - Bottom + 1) * Rnd + Bottom)
strRecordID = R![Loan_Record_ID]
T.AddNew
T![Loan_Record_ID] = strRecordID
T.Update
Next
End Sub

I guess you are mixing up Top and Bottom. Also, do a MoveLast on the
recordset before using the RecordCount property.

....
R.MoveLast
Top = R.RecordCount - 1
Bottom = 1
....

HTH
Matthias Kläy