Choose a Name at Random

[e_loveall]

I found what I was looking for in Excel on this topic, but I wonder if it transfers to Access...

We keep track of all our program participants in Access tables. I was wondering if there is code that will randomly choose a name from the table, like picking a name out of a hat. It would have to choose both the first name and corresponding last name, which are in separate fields.

Thanks!

 

[Joe4]

OK, here is one way. I recreated a example.

I assume that you have a table named "tblNames" with two fields:
1. ID
2. Name

The "ID" field is a counter, so the first record is 1, the second is 2, etc. with no gaps.

The "Name" field is the name of each person

Then create a new query named "qryPickOne". Add the "tblNames" table and the fields to this query. This is the query that we will use select a random person.

Then create a form and add a command button to the form. Name the command button "cmdPickRandom". To the OnClick property of this button, add the following VBA code:

Private Sub cmdPickRandom_Click()

    Dim myCount As Long
    Dim myRandomNumber As Long
    Dim mySQL As String
    
'   Enter number of records
    myCount = 5
'   Calculate random number
    myRandomNumber = Int(Rnd * myCount) + 1
'   Build SQL string to pull number
    mySQL = "SELECT tblNames.* FROM [tblNames] WHERE (((tblNames.ID)=" & myRandomNumber & "));"
'   Attach SQL code to query
    CurrentDb.QueryDefs("qryPickOne").SQL = mySQL
'   Open Query
    DoCmd.OpenQuery "qryPickOne", acViewNormal, acEdit
   
End Sub

Now, whenever you click this command button it will select a random person via the query, open the query, and display your result.

Note that manually hard-coded the number of records in the VBA code above ("5"). If you like, we can also get this number via VBA code also.

Modify as needed.

 

[Joe4]

Here is another link that has a different example. I don't think this one requires you to use a ID/counter field:
http://www.accessvba.com/showthread.php?s=&threadid=2533

It is a little different, as they are looking to pull out a certain percent of records, but the premise should be the same.

 

[e_loveall]

It works great! Thank you so much.