Return a random record from a DAO Recordset
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
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.