Results 1 to 2 of 2
  1. #1
    wasim_sono is offline Advanced Beginner
    Windows XP Access 2013 64bit
    Join Date
    May 2005

    random record

    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.



  2. #2
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007

    Quick and Dirty Code

    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
    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

    'Display Winner's Name
    MsgBox rst2![EmplName] & " is a winner"

    'Additional housekeeping - close the other recordset
    Set rst2 = Nothing

    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Deleting Record Contents, not complete record...
    By Charles Waters in forum Access
    Replies: 2
    Last Post: 06-24-2008, 12:00 PM
  2. generating random numbers on form
    By anitra in forum Forms
    Replies: 1
    Last Post: 02-14-2006, 10:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums