ACC: Sample Function to Return a Random Record

ID: Q108435

The information in this article applies to:

• 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

   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

   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:

   ARTICLE-ID: Q128874
   TITLE:      ACC: Find N Records in Random Order

Additional query words:

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

Last Reviewed: November 20, 1998