Random Generating of Test Questions in Access using VBA Script

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.
LVL 5
rebejonesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dpalmeCommented:
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
dpalmeCommented:
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
rebejonesAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dpalmeCommented:
*&(*& 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
dpalmeCommented:
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
rebejonesAuthor Commented:
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
dpalmeCommented:
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
rebejonesAuthor Commented:
Ok I tried doing a query and it is giving me all the questions back.  Any suggestions on where to begin.
0
rebejonesAuthor Commented:
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
dpalmeCommented:
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
dpalmeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rebejonesAuthor Commented:
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
rebejonesAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.