Dear All
I have an employees database in which I want to draw a rancdom record for lucky draw. How I use my table to extract a random record on basis of empl no.
Thanks.
Wasim
Dear All
I have an employees database in which I want to draw a rancdom record for lucky draw. How I use my table to extract a random record on basis of empl no.
Thanks.
Wasim
Some code you could try with your field names...
Sub RandomPick()
Dim db As Database
Dim rst, rst2 As Recordset
Dim intMin, intMax, intWinner As Integer
Dim bool_NoWinner As Boolean
bool_NoWinner = True
'Create a recordset to find the lowest and highest record values
Set db = CurrentDb
Set rst = db.OpenRecordset("Select Min([empl no]) as MinEmp, Max([empl no]) as MaxEmp from tblEmployees")
'Assign recordset minimum and maximum record numbers to variables
intMin = rst![MinEmp]
intMax = rst![MaxEmp]
'Housekeeping - Close recordset
rst.Close
Set rst = Nothing
'Randomly pick a winner based on the minimum and maximum possible values
'If no winner is found, loop back and try again
Do While bool_NoWinner
winner = Int((intMax - intMin) * Rnd) + intMin
Set rst2 = db.OpenRecordset("Select * from tblPatient Where Patient_ID = " & winner)
If rst2.EOF Then
bool_NoWinner = False
End If
Loop
'Display Winner's Name
MsgBox rst2![EmplName] & " is a winner"
'Additional housekeeping - close the other recordset
rst2.Close
Set rst2 = Nothing
End Sub