ACC: Sample Function to Return a Random Record

MORE INFORMATION

1. Create a module and type the following line in the Declarations section if it is not already there:

   Option Explicit

2. Type the following procedure:
   
   In Microsoft Access 2.0, 7.0 and 97:

         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()
         'change DB_Open_Dynaset to DBOpenDynaset for Access 97
         Set MyRS = MyDB.OpenRecordset(RecordSetName, DB_Open_Dynaset)
         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
   
      In Microsoft Access 1.x:
   
         Function FindRandom (RecordSetName As String, Fieldname As String)
         Dim MyDB As Database
         Dim MyRS As DynaSet
         Dim SpecificRecord As Long, i As Long, NumOfRecords As Long
   
         Set MyDB = CurrentDB()
         Set MyRs = MyDB.CreateDynaset(RecordSetName)
         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
   						

3. To test this function, type the following line in the Debug window (or the Immediate window in versions 1.x and 2.0), and then press ENTER.

   ?FindRandom("<RecordSetName>", "<FieldName>")

   where <RecordSetName> is the name of your recordset and <FieldName> is the name of a field in your recordset.
   
   Note that each time you run the function, a different record will be returned.