hello all,
I am having a maddingly frustrating issue with an application I am trying to make an I have no idea what I am doing wrong! AHHHHHH! My ultimate goal is to make a quiz generator. I want one correct answer in a button and three incorrect answers in the other buttons. The placement of the correct and incorrect answers should be randomly placed. The issue seems to be how I am using the MyAnswerValue variable. I keep re-using the index value no matter what I seem to do!
Book1 holds test results from a full test of the VBA code. As you will note, D column has a duplicate 1 value. What I want are the values 1,2,3, and 4 to be randomly selected but only used once. The Cells of A1, B1, C1, and D1 contain explanations of what they are and how I am getting them.
I have also placed my Access database file in the Zip file. The code that I am trying to troubleshoot is in the Form_QuizForm
Code:
Option Compare Database
Private Sub Form_Load()
Dim dbs As DAO.Database
Dim rsQuery As DAO.Recordset
Dim myCol As Collection
Set myCol = New Collection
Dim AnswerCol As Collection
Set AnswerCol = New Collection
Set dbs = CurrentDb
AnswerCol.Add 1, "Answer1"
AnswerCol.Add 2, "Answer2"
AnswerCol.Add 3, "Answer3"
AnswerCol.Add 4, "Answer4"
'Open a dynaset-type Recordset using a saved query
Set rsQuery = dbs.OpenRecordset("All_Records", dbOpenDynaset)
rsQuery.MoveFirst
Do While Not rsQuery.EOF
myCol.Add rsQuery.Fields("ID").Value
rsQuery.MoveNext
Loop
'Debug.Print "Start"
'Debug.Print myCol(3)
'Debug.Print myCol.Count
'myCol.Remove (3)
'Debug.Print myCol.Count
'myCol.Add "Retry"
'Debug.Print myCol.Count
'Debug.Print "End"
counter = 0
While counter <= 4
' The below line selects a random value between the minimum and maximum values in the Collection.
MyValue = Int((myCol.Count * Rnd) + 1) ' <- This is the whole recordset count
' Debug.Print myCol.Count & "Max Count"
' Debug.Print myCol(MyValue) & "Current Integer"
rsQuery.Filter = "ID = " & myCol(MyValue) & "" ' <-This is creating a new record selection object using a random index number from the whole recordset.
rsQueryFiltered = rsQuery.OpenRecordset ' <-This is opening recordset based on the filtering criteria.
' Debug.Print rsQueryFiltered!ID & " " & rsQueryFiltered!Question & " " & rsQueryFiltered!Answer
MyAnswerValue = Int((AnswerCol.Count * Rnd) + 1)
FieldCaption = "Answer" & MyAnswerValue
If counter = 0 Then
' MyAnswerValue = Int((AnswerCol.Count * Rnd) + 1)
' FieldCaption = "Answer" & MyAnswerValue
Me.QuestionField.Value = rsQueryFiltered!Question
Forms("QuizForm").Controls(FieldCaption).Caption = rsQueryFiltered!Answer
Debug.Print myCol.Count & " " & MyValue & " " & AnswerCol.Count & " " & MyAnswerValue
AnswerCol.Remove FieldCaption
ElseIf counter = 1 Then
' MyAnswerValue = Int((AnswerCol.Count * Rnd) + 1)
' FieldCaption = "Answer" & MyAnswerValue
'Me.QuestionField.Value = rsQueryFiltered!Question
Forms("QuizForm").Controls(FieldCaption).Caption = rsQueryFiltered!Answer
Debug.Print myCol.Count & " " & MyValue & " " & AnswerCol.Count & " " & MyAnswerValue
AnswerCol.Remove FieldCaption
ElseIf counter = 2 Then
' MyAnswerValue = Int((AnswerCol.Count * Rnd) + 1)
' FieldCaption = "Answer" & MyAnswerValue
'Me.QuestionField.Value = rsQueryFiltered!Question
Forms("QuizForm").Controls(FieldCaption).Caption = rsQueryFiltered!Answer
Debug.Print myCol.Count & " " & MyValue & " " & AnswerCol.Count & " " & MyAnswerValue
AnswerCol.Remove FieldCaption
ElseIf counter = 3 Then
' MyAnswerValue = Int((AnswerCol.Count * Rnd) + 1)
' FieldCaption = "Answer" & MyAnswerValue
'Me.QuestionField.Value = rsQueryFiltered!Question
Forms("QuizForm").Controls(FieldCaption).Caption = rsQueryFiltered!Answer
Debug.Print myCol.Count & " " & MyValue & " " & AnswerCol.Count & " " & MyAnswerValue
AnswerCol.Remove FieldCaption
End If
Stop
myCol.Remove (MyValue) ' <-This is eliminating a used value from the Collection.
' Stop
' myCol.Remove (MyValue)
counter = counter + 1
Wend
End Sub