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


  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
For more information about the Int() function or the Rnd() function, search the Help Index for Int or Rnd, or ask the Microsoft Access 97 Office Assistant.

Additional query words:

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


Last Reviewed: November 11, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.