Attribute VB_Name = "GetRandomData"
Option Compare Database
Option Explicit

Sub PickRandom()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strTableName As String
    
    strSQL = "SELECT tblStaff.Firstname, tblStaff.Lastname " & _
             "INTO tblTemp " & _
             "FROM tblStaff;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    
    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblTemp")
    Set fld = tdf.CreateField("RandomNumber", dbDouble)
    
    tdf.Fields.Append fld

    Set rst = db.OpenRecordset("tblTemp", dbOpenTable)
    
    rst.MoveFirst
    Do
        Randomize
	rst.Edit
            rst![RandomNumber] = Rnd()
        rst.Update
        rst.MoveNext
    Loop Until rst.EOF
    
    rst.Close
    Set rst = Nothing
    
    strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy")
    strSQL = "SELECT TOP 25 tblTemp.Firstname, tblTemp.Lastname " & _
             "INTO " & strTableName & " " & _
             "FROM tblTemp " & _
             "ORDER BY tblTemp.RandomNumber;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    db.TableDefs.Delete ("tblTemp")
End Sub