none
Looping Through All Combo Boxes on a Form RRS feed

  • Question

  • Hi, I'm trying to build a simple quiz application which gives the user some questions and then 4 possible multiple choice answers for each question.  Every combo box for every question has A-D as possible answers and then if the answer selected = txtAnswerCorrect (not visible on the form) then I count that as a correct answer and add that to intCorrectAnswers.  It all works fine for just one question at a time but when I try loop through every question on the whole form, I get the error "you can't go to the specified record" on this line DoCmd.GoToRecord Record:=acNext. 

    Private Sub btnGradeExam_Click()
    
    Dim frm As Form
    Dim ctl As Control
    Dim intNumberOfQuestions As Integer
    Dim intCorrectAnswers As Integer
    Dim intLoopCount As Integer
    
    intNumberOfQuestions = 0
    intCorrectAnswers = 0
    intLoopCount = 0
    
    'Counts the number of questions on the form
    Dim rst As Object
    Set rst = Me.RecordsetClone
    On Error Resume Next
    rst.MoveLast
    On Error GoTo 0
    intNumberOfQuestions = rst.RecordCount
    
    Set frm = Forms![frm Quizzes]
    
    Do While intLoopCount < intNumberOfQuestions
        For Each ctl In frm.Controls
            If ctl.ControlType = acComboBox Then
                If ctl.Value = txtAnswerCorrect Then
                    intCorrectAnswers = intCorrectAnswers + 1
                End If
            End If
            'DoCmd.GoToRecord , , acNext
            'DoCmd.GoToRecord Record:=acNext
        Next
        intLoopCount = intLoopCount + 1
        DoCmd.GoToRecord Record:=acNext 'Error occurs here
    Loop
    MsgBox ("Correct answers " & intCorrectAnswers)
    
    End Sub

    Wednesday, July 22, 2015 4:12 PM

Answers

  • Hi,

    Just a guess but are you using a Continuous View form to display all the questions on the form? If so, you can't "loop" through all the comboboxes for each question because there is only actually one combobox for the entire set of questions. Instead, you can just use the name of the combobox and then loop through the records. For example:

    Dim lngAns As Long
    
    DoCmd.GoToRecord , , acFirst
    
    Do While Not Me.NewRecord
    
      If Me.ComboboxName = Me.txtAnswerCorrect Then
     
        lngAns = lngAns + 1
    
      End If
    
      DoCmd.GoToRecord , , acNext
    
    Loop
    
    MsgBox "Correct answers: " & lngAns

    The above assumes the form is set to allow additions. Also, there's better ways to tackle this, but the above is just a quick way for you to test if the logic works. Cheers!
    • Proposed as answer by André Santo Wednesday, July 22, 2015 5:14 PM
    • Marked as answer by ericvanburen Wednesday, July 22, 2015 5:48 PM
    Wednesday, July 22, 2015 5:04 PM
  • I'm not sure what you are attempting to do there in your code.  The logic doesn't make sense.

    I will assume that you have a table structure similar to the following in your db:

    Your test form should display the test questions from the table evTestQuestions.

    Also on your test form, below each queston, you should display the list of possible answers from the table evQuestionAnswers.

    When the student selects an answer and saves his/her selection, a record should be added to the table evTestQandA.

    When the test is complete, you simply query the table evTestQandA joined with evQuestionAnswers and evTestQuestions.  The query should count all the answers where evQuestionAnswers.IsCorrect = True and evTestQuestions.TestID = The current test id being tested, and StudentID = The current student being tested.


    • Edited by RunningManHD Wednesday, July 22, 2015 5:36 PM
    • Marked as answer by ericvanburen Wednesday, July 22, 2015 5:48 PM
    Wednesday, July 22, 2015 5:14 PM

All replies

  • Hi,

    Just a guess but are you using a Continuous View form to display all the questions on the form? If so, you can't "loop" through all the comboboxes for each question because there is only actually one combobox for the entire set of questions. Instead, you can just use the name of the combobox and then loop through the records. For example:

    Dim lngAns As Long
    
    DoCmd.GoToRecord , , acFirst
    
    Do While Not Me.NewRecord
    
      If Me.ComboboxName = Me.txtAnswerCorrect Then
     
        lngAns = lngAns + 1
    
      End If
    
      DoCmd.GoToRecord , , acNext
    
    Loop
    
    MsgBox "Correct answers: " & lngAns

    The above assumes the form is set to allow additions. Also, there's better ways to tackle this, but the above is just a quick way for you to test if the logic works. Cheers!
    • Proposed as answer by André Santo Wednesday, July 22, 2015 5:14 PM
    • Marked as answer by ericvanburen Wednesday, July 22, 2015 5:48 PM
    Wednesday, July 22, 2015 5:04 PM
  • I'm not sure what you are attempting to do there in your code.  The logic doesn't make sense.

    I will assume that you have a table structure similar to the following in your db:

    Your test form should display the test questions from the table evTestQuestions.

    Also on your test form, below each queston, you should display the list of possible answers from the table evQuestionAnswers.

    When the student selects an answer and saves his/her selection, a record should be added to the table evTestQandA.

    When the test is complete, you simply query the table evTestQandA joined with evQuestionAnswers and evTestQuestions.  The query should count all the answers where evQuestionAnswers.IsCorrect = True and evTestQuestions.TestID = The current test id being tested, and StudentID = The current student being tested.


    • Edited by RunningManHD Wednesday, July 22, 2015 5:36 PM
    • Marked as answer by ericvanburen Wednesday, July 22, 2015 5:48 PM
    Wednesday, July 22, 2015 5:14 PM
  • Hi. 

    Yes, I am using a continuous form and your code works well. 

    Thank you

    Wednesday, July 22, 2015 5:47 PM
  • This looks like a better structure than what I have now.   I'll give this a try.  Thanks
    Wednesday, July 22, 2015 5:48 PM
  • Hi. 

    Yes, I am using a continuous form and your code works well. 

    Thank you

    Hi,

    You're welcome! Glad we could assist. Good luck with your project.

    Wednesday, July 22, 2015 11:50 PM
  • This looks like a better structure than what I have now.   I'll give this a try.  Thanks

    The following is a link to a sample db using the structure above.

    https://onedrive.live.com/redir?resid=E55A76D6EB477199!114&authkey=!AINHLny3IrQk3bM&ithint=file%2caccdb

    Thursday, July 23, 2015 3:29 AM