[Webinar] Streamline your web hosting managementRegister Today


Random Question

Posted on 2002-07-09
Medium Priority
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
  • 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.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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 800 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"
rstQuestions.open 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)
  rstQuestions.open 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 101

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

608 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