Random Question

Posted on 2002-07-09
Last Modified: 2010-05-02
I am accessing a Test Question Database application thru VB6. The database has a table with 80 questions in it. I would like for someone to give me an idea on how to generate the questions randomly from a recordset populated from the table.
Question by:global819
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +7
LVL 43

Expert Comment

ID: 7140598
Well you have a few options, you can either retrieve the entire set of questions and then use the Rnd() function to determine a random record to deal with. You could also return a random number in the recordset using RAND() for SQL and rnd() for access to give you a sort order and then process the questions sequentially.

To be able to give a more definitive answer it would help to know which database (SQL/ACCESS etc) and what kind of testing process you are trying to create, along with whether you are using DAO or ADO so that the code examples can be appropriate for your situation.

Expert Comment

ID: 7140606
A working idea, probably not so efficient:
Get all the records to a recordset, move to the first record, and then move forward using rs.movenext method a randon number of records.
LVL 14

Expert Comment

ID: 7140612
Well it would be wise to take care that questions should not be repeated for particular set.  For this one approach would be

Store the element in array generated by Rnd function
While generating next element check the existance in
array, if number exists then generate other number.
Industry Leaders: 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 43

Expert Comment

ID: 7140627
Indeed ajexpert, though if you use Rand()/Rnd() in the SQL statement that returns the records in a sorted order then you don't have to worry about this.

Accepted Solution

rpai earned 200 total points
ID: 7140666
You could do something like this:-

oConn.ConnectionString = "DSN=myDSN;UID=myUID;pwd=myPWD"
'-- Client cursor required to enable AbsolutePosition property
oConn.CursorLocation = adUseClient

Set oCmd.ActiveConnection = oConn
oCmd.CommandText = "SELECT * FROM tbl_TestQuestions"
Set oRs = oCmd.Execute

'-- This would generate random numbers between 1 and 80
intValue = (Int(80 * Rnd) + 1)

'-- Set the pointer to the record in the RecordSet
oRs.AbsolutePosition = intValue

'-- List all the fields for that record
For iCount = 0 To oRs.Fields.Count - 1
    Debug.Print oRs.Fields(iCount).Value

'-- Release all the references to the objects used


Expert Comment

ID: 7140790
In addition to rpai's comment, you must save all the questions that are chosen to an array. So that every time you choose a random question you firstly check whether it exists in that array.

LVL 22

Expert Comment

ID: 7141203
What comes to my mind is to retrieve all primary keys into a dynamic array and shuffle them (see below.)  This would be DB-independent.

Now it becomes a simply task to use the next primary key in sequence to get the next "Random" question.

Sample (partially pseudo-)code:
' create array
dim lQKey() as long
dim lQCount as long

'open DB connection
strSQL = "Select IDKey from tblQuestions" strSQL, connectionstring

lQCount = 0
while not rstQuestions.EOF
  redim lQKey(lQCount)
  lQKey(lQCount) = rstQuestions.Fields("IDKey").Value
  lQCount = lQCount + 1

' Shuffle IDs
for i=0 to lQCount-1
  newi = rnd*lQCount
  temp = lQKey(i)
  lQKey(i) = lQKey(newi)
  lQKey(newi) = temp
next i


' Retieve Qs
dim i as integer
dim mbxAns as vbMsgboxResult

for i=0 to lQCount - 1
  strSQL = "SELECT * FROM tblQuestions WHERE IDKey = " & lQKey(i) strSQL, connectionstring
  if rstQuestions.EOF then  
    ' msgbox "Question " & lQKey(i) & " is missing!  Did someone just delete it?"
    mbxAns = msgbox (rstQuestions.Fields("QuestionText").Value, vbYesNo)
    ' Answer processing added here
next i
LVL 18

Expert Comment

ID: 7141220
If you want to make sure that you don't repeat any questions, and you want them in a random order, then you can use an algorithm I set up for shuffling a deck of cards.  Here is the pseudo-code.  Let me know if you need the code implementation:

Select all of your question into a recordset (you can order them by question_id if you want, but not important)

Declare a variable as a collection:

Dim oQuestions as New Collection

Then, move through the recordset using AddItem to add the question_ids to the collection

While Not RS.EOF
   oQuestions.AddItem RS("Question_ID").Value

Then, randomly pull items out of the collection into another array:

Dim i as Long
Dim aQuestions() as Long

Redim aQuestions(0) as long

While oQuestions.Count > 0
'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
   i = Int(((oQuestions.Count - 1) - 0 + 1) * Rnd + 0)
   aQuestions(UBound(aQuestions)) = oQuestions(i)
   oQuestions.RemoveItem i
   if oQuestions.Count > 0 then
      Redim Preserve aQuestions(UBound(aQuestions) + 1) as long
   End if

When this is done, aQuestions will have a list of randomly selected question_ids

You can then sequentially read through the aQuestions array.

LVL 100

Expert Comment

ID: 7141282

Expert Comment

ID: 7141420
Private Function GetUniqueValue() As Variant
Dim sTemp as String
Dim i As Integer
Dim intValue As Integer
Dim vCounter As Variant
sTemp = "X"
   ReDim vCounter(10)
    For i = 1 To 10
        intValue = Int(10 * Rnd) + 1
        If InStr(sTemp, CStr(intValue)) = 0 Then
            sTemp = sTemp & " " & intValue
            Debug.Print i & vbTab & intValue & vbTab & sTemp
            vCounter(i) = intValue
            i = i - 1
        End If
GetUniqueValue = vCounter
End Function
LVL 18

Expert Comment

ID: 7141730
PS, I'm guessing that the collection indexes start at 0 and go to count - 1, but it's possible that they are 1 based, if so, then this statement would change to:

'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
 i = Int((oQuestions.Count - 1 + 1) * Rnd + 1)
LVL 49

Expert Comment

ID: 8002228
Hi global819,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept rpai's comment(s) as an answer.

global819, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
DanRollins -- EE database cleanup volunteer

Expert Comment

ID: 8094215
Comment from expert accepted as answer

E-E Admin

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

735 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