How to: Return a Random Record from a DAO Recordset

  • Article
Access Developer Reference

Microsoft Access does not have a built-in mechanism for returning a random record from a set of records. This topic describes a sample user-defined function that you can use to return a random record.

  Function FindRandom (RecordSetName As String, Fieldname As String)

Dim MyDB As Database
Dim MyRS As Recordset
Dim SpecificRecord As Long, i As Long, NumOfRecords As Long


Set MyDB = CurrentDB()
Set MyRS = MyDB.OpenRecordset(RecordSetName, dbOpenDynaset)
On Error GoTo NoRecords
MyRS.MoveLast
NumOfRecords = MyRS.RecordCount
SpecificRecord = Int(NumOfRecords * Rnd)
If SpecificRecord = NumOfRecords Then
SpecificRecord = SpecificRecord - 1
End If
MyRS.MoveFirst
For i = 1 To SpecificRecord
MyRS.MoveNext
Next i
FindRandom = MyRS(Fieldname)
Exit Function


NoRecords:
If Err = 3021 Then
MsgBox "There Are No Records In The Dynaset", 16, "Error"
Else
MsgBox "Error - " & Err & Chr$(13) & Chr$(10) & Error, _
16, "Error"
End If
FindRandom = "No Records"
Exit Function


End Function