how to retrieve a value from a field by index?

[RedGoneWILD]

Hi,

I have a random number generated based on a query's record count. I would then like to retrieve a field value from that query based on the index that is generated.



The code below works and generates a random number based on the query's record count. But I do not know how to retrieve the EmployeeID field with the random index generated. I'm not sure if I need to open a recordset. I basically just want to go right to the record index that is generated and retrieve the EmployeeID, without having to loop through the records. Is this possible?

Private Sub btnRandomEmployeeName_Click()
    Dim randomEmployeeNum As Integer
    randomEmployeeNum = Int((EmployeeCount - 1 + 1) * Rnd + 1)
    MsgBox randomEmployeeNum
End Sub

Private Function EmployeeCount() As Integer
    EmployeeCount = DCount("[EmpID]", "qryActiveEmployees")
End Function

[Rod]

From the horse's mouth!

How to: Return a Random Record from a DAO Recordset
Microsoft Access does not have a built-in mechanism for returning a random record from a set of records. This topic describes a sample user-defined function that you can use to return a random record.

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() Set MyRS = MyDB.OpenRecordset(RecordSetName, dbOpenDynaset) 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

[RedGoneWILD]

It works, thanks! I needed to change the following:

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset

[RedGoneWILD]

There is actually something about this function that is not random at all. Every time I close the program, and open it back up, it generates the same records. I remember something in VB about it going off the system time, so the only way it could repeat itself is if you call the function at exactly the same time every 24 hours, but what's going on here? All I have to do is close the program and open it back up and it will generate the exact same records as last time. Is there a variable or something I need to set it to ensure it's completely random?

[Rod]

It seems that even Microsoft doesn't know what it's doing. The sample code was taken straight from an MS web page but here are the notes related to Rnd:

Remarks
The Rnd function returns a value less than 1 but greater than or equal to zero.
The value of number determines how Rnd generates a random number:
For any given initial seed, the same number sequence is generated because each successive call to the Rnd function uses the previous number as a seed for the next number in the sequence.
Before calling Rnd, use the Randomize statement without an argument to initialize the random-number generator with a seed based on the system timer.

I suggest therefore you place an Randomize statement before the use of Rnd

Randomize
SpecificRecord = Int(NumOfRecords * Rnd)

Sorry to mislead you.

[orange]

Further to Rod's posts, here is an older TechRepublic article by Susan Harkins on this subject
http://www.techrepublic.com/blog/how...oft-access/149

[RedGoneWILD]

Randomize worked, and thanks for the article. bookmarked

Thanks!