By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 1,987 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

To get a Random Question

P: 19
i'm using ms access and i have a table in my database which consists of fields(questionno,question,option1,option2,option3 ,option4,answer).It has some 500 (say)records.
i created a form(named exam) using wizard from that table.i have a command button (next) in the form.when i press next button records from that table r coming one by one.(i need those records come at random).
previous to the exam form ,i have 1 form named examtype. In examtype form i have some options that is i can select test with 10 questions or 20 or 30 or like that.
suppose if i select option test with 10 questions ,then 10questions have to come on the exam form randomly from that table.hHow to do this?
can anyone help me....
Thanks in advance.
Mar 22 '07 #1
Share this Question
Share on Google+
12 Replies


ADezii
Expert 5K+
P: 8,638
i'm using ms access and i have a table in my database which consists of fields(questionno,question,option1,option2,option3 ,option4,answer).It has some 500 (say)records.
i created a form(named exam) using wizard from that table.i have a command button (next) in the form.when i press next button records from that table r coming one by one.(i need those records come at random).
previous to the exam form ,i have 1 form named examtype. In examtype form i have some options that is i can select test with 10 questions or 20 or 30 or like that.
suppose if i select option test with 10 questions ,then 10questions have to come on the exam form randomly from that table.hHow to do this?
can anyone help me....
Thanks in advance.
There is a routine that I created that does almost exactly what you are requesting but I do not have it readily available. As soon as I get the chance, I'll be happy to post it for you. Stay tuned.
Mar 22 '07 #2

ADezii
Expert 5K+
P: 8,638
i'm using ms access and i have a table in my database which consists of fields(questionno,question,option1,option2,option3 ,option4,answer).It has some 500 (say)records.
i created a form(named exam) using wizard from that table.i have a command button (next) in the form.when i press next button records from that table r coming one by one.(i need those records come at random).
previous to the exam form ,i have 1 form named examtype. In examtype form i have some options that is i can select test with 10 questions or 20 or 30 or like that.
suppose if i select option test with 10 questions ,then 10questions have to come on the exam form randomly from that table.hHow to do this?
can anyone help me....
Thanks in advance.
This code generates a Test involving a Random Number of Questions that are determined by the User. a Yes/No Field [OK] in tblQuestions sets the actual status of the Random Questions:
Expand|Select|Wrap|Line Numbers
  1. Const pMAXIMUM_NO_QUESTIONS As Integer = 100      'Max# of Questions
  2. Dim intAvailableQuestions As Integer, Msg As String, intNumberOfTestQuestions As Integer
  3. Dim Counter As Integer, MyRandom As Integer, T As Integer
  4.  
  5. 'How many Questions are actually available?
  6. intAvailableQuestions = DCount("*", "tblQuestions")
  7. Msg = "Enter the Number of Questions to be Tested on up to a (MAXIMUM of" & Str$(pMAXIMUM_NO_QUESTIONS) & ")" & vbCrLf & vbCrLf
  8. Msg = Msg & "NOTE: There are " & Str$(intAvailableQuestions) & " possible Questions in the Database, choose wisely!"
  9.  
  10. 'Have the User somehow Input the number of Questions to be tested on
  11. intNumberOfTestQuestions = Val(InputBox$(Msg, "Random Test Generator"))
  12.  
  13. 'Validate the Input
  14. If intNumberOfTestQuestions = 0 Or intNumberOfTestQuestions < 1 Then    'Cancel Button pressed, no value entered,
  15.   Exit Sub                                                              'entry < 1, String entered
  16. ElseIf intNumberOfTestQuestions > 100 Then
  17.   MsgBox "The Maximum number of Test Questions that can be entered is" & Str$(pMAXIMUM_NO_QUESTIONS), vbExclamation, "Random Test Generator"
  18.   Exit Sub
  19. End If
  20.  
  21. 'Reset all [OK] Fields to False
  22. DoCmd.OpenQuery "qrySetOKFieldToFalse", acNormal, acEdit
  23.  
  24. 'Must be a valid entry at this point, start generating the Random Numbers
  25. Dim MyDB As Database, MyRS As Recordset
  26. ReDim RandomNumbers(1 To intNumberOfTestQuestions) As Integer
  27.  
  28. Randomize   'Seed the Random Number Generator
  29.  
  30. 'Generate intNumberOfTestQuestions Random numbers between 1 and intAvailableQuestions
  31. For T = 1 To intNumberOfTestQuestions
  32.     MyRandom = Int(Rnd() * intAvailableQuestions + 1)
  33.      'Store Random Question Numbers in an Array
  34.       RandomNumbers(T) = MyRandom
  35. Next T
  36.  
  37. 'Lets eliminate the Duplicates - there may be some
  38. DoItAllOverAgain:
  39. For Counter = 1 To UBound(RandomNumbers)
  40.   For T = 1 To UBound(RandomNumbers)
  41.     If Counter <> T Then
  42.       If RandomNumbers(Counter) = RandomNumbers(T) Then
  43.         RandomNumbers(Counter) = Int(Rnd() * intAvailableQuestions + 1)
  44.         GoTo DoItAllOverAgain          'Shouldn't really use but was just lazy
  45.       Else
  46.       End If
  47.     End If
  48.   Next T
  49. Next Counter
  50.  
  51. Set MyDB = CurrentDb
  52. Set MyRS = MyDB.OpenRecordset("tblQuestions", dbOpenDynaset)
  53.  
  54. For Counter = 1 To UBound(RandomNumbers)
  55.   MyRS.MoveFirst    'Start from 1st Record, always return
  56.   If RandomNumbers(Counter) = 1 Then   '1st Record - don't Move! @ the 1st
  57.     MyRS.Edit
  58.     MyRS![OK] = True    'Set the [OK] Field to True
  59.     MyRS.Update
  60.   ElseIf RandomNumbers(Counter) = intAvailableQuestions Then    'Special provision for the Last Record
  61.     MyRS.MoveLast
  62.     MyRS.Edit
  63.     MyRS![OK] = True
  64.     MyRS.Update
  65.   Else
  66.     MyRS.Move RandomNumbers(Counter) - 1 'Move to the Record indicated by the Random Number
  67.     MyRS.Edit
  68.     MyRS![OK] = True
  69.     MyRS.Update
  70.   End If
  71. Next Counter
  72.  
  73. MyRS.Close
  74.  
  75. 'Reached the End of the Trail. The Report will display all Questions where [OK]=True
  76. DoCmd.OpenReport "rptTestQuestions", acViewPreview
Mar 23 '07 #3

P: 19
sir,
Thank u very much for ur reply.
But i want those questions in the form not in the report.in the code u wrote openreport.That form is created using wizard from the tablequestions.in that form i have next button when i press it next question has to come randomly.
i copied that code and tried but mydb as database user datatype not defined error is coming.how to solve that error.can i get questions on the form randomly instead of in report.
please help me....
Mar 23 '07 #4

Rabbit
Expert Mod 10K+
P: 12,366
If you need to open a form instead of a report then you can use DoCmd.OpenForm instead of the DoCmd.OpenReport.
Mar 23 '07 #5

ADezii
Expert 5K+
P: 8,638
sir,
Thank u very much for ur reply.
But i want those questions in the form not in the report.in the code u wrote openreport.That form is created using wizard from the tablequestions.in that form i have next button when i press it next question has to come randomly.
i copied that code and tried but mydb as database user datatype not defined error is coming.how to solve that error.can i get questions on the form randomly instead of in report.
please help me....
As stated by Rabbit, you could use DoCmd.OpenForm to open the Form and modify the code to change its RecordSource.
Mar 23 '07 #6

P: 19
sir,
i copied the code.i am getting error that dim MYDB as database user defined type not defined.i am unable to avoid that errort. in references also i checked.microsoft active objects library is choosed.
please help me.
Mar 26 '07 #7

ADezii
Expert 5K+
P: 8,638
sir,
i copied the code.i am getting error that dim MYDB as database user defined type not defined.i am unable to avoid that errort. in references also i checked.microsoft active objects library is choosed.
please help me.
You probably also have a Reference to the ADODB Object Library. Explicitly Declare MyDB, and any Recordset Object as in:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim MyRS As DAO.Recordset
Mar 26 '07 #8

P: 19
sir
i tried in that way also i.e. Dim MYDB as DAO.database
Dim MYRS as DAO.recordset.
But still i got user type not defined compile error.
please help me.
Mar 27 '07 #9

ADezii
Expert 5K+
P: 8,638
sir
i tried in that way also i.e. Dim MYDB as DAO.database
Dim MYRS as DAO.recordset.
But still i got user type not defined compile error.
please help me.
Set a Breakpoint on the 1st line of executable code in the Procedure, then single step (F8) through it. This Method will pinpoint the exact line giving you the error.
Mar 27 '07 #10

P: 19
sir,
Set MyRS = MYDB.OpenRecordset("tblexam", dbOpenDynaset) am getting run time error type mismatch in the above statement.
my tblexam consists of questionno(number),question(memo),option1(memo),op tion2(memo),option3(memo),option4(memo),answer(num ber),OK (yes/no)fields in the table.
please help me
thanks in advance
Mar 28 '07 #11

ADezii
Expert 5K+
P: 8,638
sir,
Set MyRS = MYDB.OpenRecordset("tblexam", dbOpenDynaset) am getting run time error type mismatch in the above statement.
my tblexam consists of questionno(number),question(memo),option1(memo),op tion2(memo),option3(memo),option4(memo),answer(num ber),OK (yes/no)fields in the table.
please help me
thanks in advance
Was Set MyRS = MYDB.OpenRecordset("tblexam", dbOpenDynaset) prefixed by?
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Set MyDB = CurrentDB()
Mar 28 '07 #12

P: 19
sir,
i got random questions.thank u for the reply.
But if the table questions consists of subjectcode field also there,there are various subcodes(say some 7).if i want 3 questions randomly from 1subject randomly,4questions from another,6 from other like that ,how i have to modify the code.subjectcode field is there in the table along with question ,options fields.
thanks in advance
Apr 2 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.