ACC: Sample Function to Return a Random Record

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

MORE INFORMATION

The following sample function will return a random record using the recordset name and the field name that you provide.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

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.

REFERENCES

For more information about using a query to return random records, please see the following article here in the Microsoft Knowledge Base:

Q128874 ACC: Find N Records in Random Order

Additional query words:

Keywords : kbprg
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbinfo