create maketable query populating random records (1 Viewer)

[Tacamo]

I am trying to use a form input to select n number of random records from a table and populate a make-table query. All the code is performed during an "on click" event. The code is below:

Dim dbs As Database
Dim rst As Recordset
Dim Maxrecords As Integer
Dim NO_QUESTIONS
Dim POSITION
Dim type_question
Dim I As Integer
Dim RANDOM_QUESTION

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Master")Maxrecords = rst.RecordCount 'determine records in table
rst.Close
Set dbs = Nothing

NO_QUESTIONS = QA.Value 'The n number of questions desired


If NO_QUESTIONS > Maxrecords Then
MsgBox "THE NUMBER OF QUESTIONS SELECTED EXCEEDS THE MAXIMUM AVAILABLE, RE-SELECT"
GoTo Exit_Command5_Click:
End If

POSITION = Combo41.Value 'determines which category of questions (filter1)

If OPTION_GROUP_TYPE.Value = 1 Then
type_question = "OPEN"
Else
type_question = "CLOSED"
End If 'Determines type questions (Filter2)

For I = 1 To NO_QUESTIONS
RANDOM_QUESTION = Int((Maxrecords * Rnd) + 1)

***Here is where I need to goto record matching the randomize statement, copy the record, and paste into a new table or query.

Next


***I guess another thing I need is to filter the list before generating randomized records as I have not used the filters1&2 to randomize the correct records selected by the input form.

Just writing this brought up more thoughts, but still have no clues. Thanks

 

[charityg]

Would it be possible to add a field to your master table that would store a random number? If so, it seems that you should have to step through each record in your recordset matching your criteria, and generate a random number in the field. Then run a maketable sql statement that chooses the top values equal to the number of questions.

Something like:
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * from Master where Position=" & yourvalue & " and type_question=" & yourvalue ";",dbopendynaset)

Maxrecords = rst.RecordCount 'determine records in table


NO_QUESTIONS = QA.Value 'The n number of questions desired


If NO_QUESTIONS > Maxrecords Then
MsgBox "THE NUMBER OF QUESTIONS SELECTED EXCEEDS THE MAXIMUM AVAILABLE, RE-SELECT"
GoTo Exit_Command5_Click:
End If

with rst
do until .eof
.edit
!fieldyoucreated= Int((Maxrecords * Rnd) + 1)
.update
.movenext
loop
end with

strsql="SELECT TOP " & Maxrecords & " Master.* INTO NewTable
FROM Master
ORDER BY Master.fieldyoucreated;"

docmd.runsql(strsql)

Or something like this. Hope this helps a little.