Results 1 to 4 of 4
  1. #1
    lonesoac0 is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Aug 2014
    Posts
    36

    Inconsistent results with using collections and adding captions to buttons

    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
    Attached Files Attached Files

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    One thing you should do is add Option Explicit to the dec's of each module and dim all your variables.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why would each question use same answer set as source for multiple choices? Shouldn't each question have its own subset of answers to draw from? A question like "Which president was assassinated at Ford Theater?" should not include battlefields as choices. What is this quiz for?

    Why is there no Question 3 in table? Can use an INSERT action in VBA immediate window to create record with that ID. Be sure to run Compact & Repair afterward.

    Have you step debugged and checked variables as code executes?

    Nothing prevents Rnd() from spitting out same number in different iterations.

    I gave up on the collections, went to array and randomized query and put code in button Click event:
    Code:
    Option Compare Database
    Option Explicit
    Option Base 1
    ___________________________________________
    
    Sub btnQuiz_Click()
    Dim rsQuery As DAO.Recordset
    Dim aryA(4)
    Dim counter As Integer
    Set rsQuery = CurrentDb.OpenRecordset("SELECT ID FROM Q_and_A WHERE ID<5 ORDER BY Rnd(ID)")
    rsQuery.MoveFirst
    For counter = 1 To 4
        aryA(counter) = rsQuery!ID
        rsQuery.MoveNext
    Next
    rsQuery.Close
    Set rsQuery = CurrentDb.OpenRecordset("SELECT TOP 4 * FROM Q_and_A ORDER BY Rnd(ID)", dbOpenDynaset)
    rsQuery.MoveFirst
    For counter = 1 To 4
        If counter = 1 Then Me.QuestionField = rsQuery!Question
        Me("Answer" & aryA(counter)).Caption = rsQuery!Answer
        rsQuery.MoveNext
    Next
    End Sub
    
    I tried feeding randomized query data to collection object which seemed to work but then dynamically referencing key name in second loop errored, said the object wasn't open.
    Last edited by June7; 10-26-2019 at 06:30 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    lonesoac0 is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Aug 2014
    Posts
    36
    June7, you got me MUCH closer to what I wanted. I used your code and then found a RndInt function that produces a random integer between two bounds. After I reviewed the resulting code, I concluded I could do some trimming. It looks like I am randomizing the question. Oh well, I do not care. It LOOKS like the answers are being randomly places in the UI. That is all I really care about. Here is the effective code:

    Code:
    Option Compare DatabaseOption Explicit
    Option Base 1
    Private Sub Form_Load()
    
    
    Dim rsQuery As DAO.Recordset
    Dim aryA(4)
    Dim counter As Integer
    Dim MyValue As Integer
    
    
    MyValue = RndInt(1, 4)
    
    
    Set rsQuery = CurrentDb.OpenRecordset("SELECT TOP 4 * FROM Q_and_A ORDER BY Rnd(ID)", dbOpenDynaset)
    rsQuery.MoveFirst
    For counter = 1 To 4
        If counter = MyValue Then
            Me.QuestionField = rsQuery!Question
        End If
        Me("Answer" & counter).Caption = rsQuery!Answer
        rsQuery.MoveNext
    Next
    
    
    End Sub
    
    
    
    
    Function RndInt(lowerbound As Integer, upperbound As Integer) As Integer
    Randomize
    RndInt = Int(lowerbound + Rnd() * (upperbound - lowerbound + 1))
    End Function
    Last edited by lonesoac0; 10-28-2019 at 08:48 AM. Reason: More comments

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update Queries with Inconsistent Results
    By Netopia in forum Modules
    Replies: 5
    Last Post: 07-10-2018, 02:26 PM
  2. Inconsistent results with a simle query
    By slickdog in forum Queries
    Replies: 4
    Last Post: 10-07-2015, 06:01 AM
  3. Inconsistent results.. please help!
    By vikghai in forum Access
    Replies: 6
    Last Post: 02-09-2014, 05:37 PM
  4. Adding times with inconsistent formatting
    By user888 in forum Access
    Replies: 4
    Last Post: 12-22-2013, 11:27 PM
  5. Replies: 1
    Last Post: 09-01-2012, 06:58 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums