Article ID: 108435
Article Last Modified on 1/18/2007
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
This article was previously published under Q108435
Moderate: Requires basic macro, coding, and interoperability skills.
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.
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.
- Create a module and type the following line in the Declarations section if it is not already there:
Option Explicit
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
- 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:
128874 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.
Keywords: kbinfo kbprogramming KB108435