Return Random Record

[ptuck]

I need some help returning a random record in Access 2002. I have tried the following solution form Microsoft, but continue to get "Type Mismatch" error on the Set MyRS = MyDB.OpenRecordset(RecordSetName, dbOpenDynaset) line. The goal is to have the program determine a winner from a drawing. Hope you guru's can help.

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

Thanks,
Paul

 

[scriverb]

Make the folloiwng red code modifications:

Dim MyDB As [RED]DAO.[/RED]Database
Dim MyRS As [RED]DAO.[/RED]Recordset

Post back if you have any other questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ptuck]

I am having problems calling the above function that I got from Microsoft. It is working when I test it from the immediate window, but can't call it. What I want to do is set a form or report to have the record source of the record it pulls.

Please help,
Paul