I am trying to generate a random sample of a population, no repeats, and can run the code in Excel with user inputs, however I am trying to run in Access and can't get my head around the code. I welcome any input...


Sub Random()
Dim dbs As Database
Set dbs = CurrentDb
dbs.Execute "CREATE TABLE TestSample " _
& "(Ident CHAR);"
Small = Forms![Revised Test Sample Size]![ID Min]
big = Forms![Revised Test Sample Size]![ID Max]
pop = Forms![Revised Test Sample Size]![Sample Size]

Dim i As Long
Dim randomIndex As Long

For i = Small To big
Randomize
randomIndex = Int(Rnd() * big) + 1
While randomIndex < Small: randomIndex = Int(Rnd() * big) + 1: Wend

dbs.Execute " INSERT INTO TestSample " _
& "(Ident) VALUES " _
& "(randomIndex.value);"
Next i

' Code from Excel
' Range(InputBox("Enter first Cell")).Resize(pop, 1) = Application.Transpose(myArray)

dbs.Close
End Sub


The code will run the randomization, I just can't populate the table with the numbers. I also need to limit the new table to the pop variable population.

My sample population can be 17 from 25 or 125 from 8600...

PLEASE HELP!!!

The problem would be much easier to see if it was properly indented. Edit your post and put the code into a code box. Go to the advanced editor, highlight the code and click the # button.

Never do this one line stuff:
While randomIndex < Small: randomIndex = Int(Rnd() * big) + 1: Wend

Sort these issues and I think you will be able to see what is wrong with your loops.