?
Solved

Random Generating of Test Questions in Access using VBA Script

Posted on 2004-11-17
13
Medium Priority
?
343 Views
Last Modified: 2010-05-02
I have a MS Word file that contains multiple choice questions.  I need help in figuring out how to take the word file and have it generate a test in Access.  For instance there are 50 questions in the word file, but I only want the test to have 30 questions.  If I can get help on where to start that would be greatly appreciated.  My supervisor mentioned using VBA, but I am not sure where to begin with it.
Thanks In Advance.
0
Comment
Question by:rebejones
  • 7
  • 6
13 Comments
 
LVL 1

Expert Comment

by:dpalme
ID: 12614927
I would say the easiest way would be to add the questions to an access database with a numeric key field.  Then you can randomly select the questions from the list with a few lines of code and then send this to a word doc or to the screen, etc.

Let me know if you do not understand or need further assistance.
0
 
LVL 1

Expert Comment

by:dpalme
ID: 12614936
The reason I suggested placing the questions and answers in Access is 1. you are talking about a very limited number of questions here so an SQL database would be overkill and 2. if you have need to change them later on you can do so easily without having the change the program at all.
0
 
LVL 5

Author Comment

by:rebejones
ID: 12615733
Thanks for replying.  So for instance I would have a table labeled Questions.  In the table are the fields QNo. (which would have the data type as an auto number), Question (which would hold the questions and have the data type as text) and Answers (which would have the data type as text) and a field for Correct Answers(that would print out a copy of the test with the correct answer)  I will not be grading the test.  

Is this correct so far...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:dpalme
ID: 12618249
*&(*& I had typed out a lengthy response and then hit the wrong key and everything went poof.

Yes, you could do it that way but here is how I would do it differently:

1. If you are going to have the SAME number of multiple choices per question it is easy:

Field1 INT Question Number
Field2 CHAR Actual Question
Field3 Char Multiple Choice 1
Field4 Char Multiple Choice 2
Field5 Char Multiple Choice 3
Field6 Char Multiple Choice 4
Field7 (Can be either INT with the correct choice or CHAR type with the ACTUAL COIRRECT ANSWER)

If the questions will have a varying number of choices then you should split the data up into at least two separate tables.

If you need further assistance just ask.....

Douglas
0
 
LVL 1

Expert Comment

by:dpalme
ID: 12618258
BY FAR the easiest way would be to keep the multiple choices the same for all questions, but that is not always what the user needs......I would have no more than 4, which is a standard for most tesitng.
0
 
LVL 5

Author Comment

by:rebejones
ID: 12628228
Yes all the questions have 4 choices.  I created my table like you said and now the question what do I do next.  Do I create a query that will randomly generate test questions and then create a report that will print them out.
0
 
LVL 1

Expert Comment

by:dpalme
ID: 12655274
My apologies I was out of town on a hunting trip for the weekend.  Yes, you can create a query to generate them or you can use a basic module to do the same thing or you can even get creative and do it in VB.

0
 
LVL 5

Author Comment

by:rebejones
ID: 12655440
Ok I tried doing a query and it is giving me all the questions back.  Any suggestions on where to begin.
0
 
LVL 5

Author Comment

by:rebejones
ID: 12655611
If I do it in VB I know I need to use a Randomize command something like:

Dim intTest As String

Randomize( )

intTest = "this is where I am not sure what to do next"
0
 
LVL 1

Expert Comment

by:dpalme
ID: 12656861
Personally I would do it in VB, it is simpler, you can if you want to contain it all within the same app, do it with a basic module but my preference would be to do it in VB.

How many questions are you wanting to generate from the list?
0
 
LVL 1

Accepted Solution

by:
dpalme earned 1000 total points
ID: 12657016
You will need to decide how many questions from the list you want to select and then use either a FOR statement:

for x = 1 to y
...
next x

or loop through the table.

It will be easier if you set an idnex on the recordset so that when you generate your random number you can immediately pull that question number.

you will want to keep your random number between 1 and the total number of your questions...so let's say you have six questions total in the table, you would do something like:

myValue = Int((6 * rnd) + 1)

This will generate a random number between 1 and 6

The keyword Randomize sets the seed....and you will want the seed number to change each time....

0
 
LVL 5

Author Comment

by:rebejones
ID: 12665230
Here is the code I came up with:   The Problem is that it doesn't open up the report.  I have to go to the reports and click on the Report that I named Test to see if it worked.  Not getting any error messages.  The other question I have is what if I don't know how many records I have how would I find that.  Also how do I delete what is in the tmp_Questions table before it starts looking for seed values.  I put in comment codes below where I think the delete and count records go.


Private Sub Command1_Click()
    On Error GoTo Error_Routine

    Dim db As Database
    Dim rs As Recordset
    Dim rs_R As Recordset
   
    Dim i As Integer
   
    Dim sgl_Random As Single
   
    Dim stDocName As String
   
'delete query goes here

    Set db = CurrentDb
    Set rs_R = db.OpenRecordset("tmp_Questions")
   
'Record Count goes here
   
    Randomize
    For i = 1 To 2
   
Duplicate:
    Randomize
    sgl_Random = Rnd
    int_Random = Int((4 * sgl_Random) + 1)
   
    rs_R.AddNew
    rs_R!QuestionNo = int_Random
    rs_R.Update
   Next i
   
    rs_R.Close
    Set rs_R = Nothing
    Set db = Nothing
   
   
   
stDocName = "Test"
If chk_Preview.Value = True Then
    DoCmd.OpenReport stDocName, acViewPreview
   
Else
    DoCmd.OpenReport stDocName, acViewNormal
   
End If

Exit_Sub:
    Exit Sub




Error_Routine:
If Err.Number = 3022 Then  'Duplicate Key code
    Resume Duplicate
End If







End Sub
0
 
LVL 5

Author Comment

by:rebejones
ID: 12665634
Ok I figured out the delete command as Follows:

'Delete records in tmp_Questions table

DoCmd.SetWarnings False
DoCmd.OpenQuery ("qryDelete_tmp_Questions")
DoCmd.SetWarnings True
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month15 days, 22 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question