Randomize table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that is controlled by a list box. I am looking to randomly
pick 9 names from this query and put into a report. I have some code that
will randomly pick 9 names, but i need it to be able to pick a single name
more than once. So instead of having a,b,c,d,e,f,g,h,i It would be more of
something like this: a, b, c, a, d, h, c, a, i.
 
If it's random, you can't force it to behave a certain way!

What's the code you're currently using?
 
I actually have figured it out. What I needed was to run a random pick from
a temporary table, move the first record from that table to another, pick
another random record, etc .... The code I have for it is as follows if
anyone else needs to know.

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
Q = 0
Do Until Q = 9
strSQL = "SELECT [random test].name, [random test].[picker id] " & _
"INTO tblTemp " & _
"FROM [random test];"
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"
strSQL = "Insert INTO " & strTableName & " " & _
"SELECT TOP 1 tblTemp.name, tblTemp.[picker id] " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.RandomNumber;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Q = Q + 1
Loop
DB.TableDefs.Delete ("tblTemp")
End Sub
 
Back
Top