Article ID: 108435 - Last Review: January 18, 2007 - Revision: 2.3

ACC: Sample Function to Return a Random Record

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
This article was previously published under Q108435
Moderate: Requires basic macro, coding, and interoperability skills.
Expand all | Collapse all

SUMMARY

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.
Back to the top

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.
Back to the top

REFERENCES

For more information about using a query to return random records, please see the following article here in the Microsoft Knowledge Base:
128874  (http://support.microsoft.com/kb/128874/EN-US/ ) 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.
Back to the top
APPLIES TO
  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
Back to the top
Keywords: 
kbinfo kbprogramming KB108435
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations