Loops

[Monica2]

Hi, I hope someone can help. I'm trying to create a scoring programme for a multiple choice test. Basically my form has 2 different sets of variables - one for the answer the testee provides (named q1 - q6; possible data a, b, c or d) and one for the score (named s1 - s6; scored 1 for correct and 0 for incorrect).

The "answer" variables I have set up as one collection (aItems), and the "score" variables as another (aScores). At this stage I am treating all answers (q1 - q6) as though "a" is the correct answer.

Access does not seem to have a problem with the first collection/loop, but doesn't seem to recognise the second collection/loop, no matter what I do????



Sub aItems()
Dim aItems As New Collection
Dim intCounter As Integer
Dim ctlA As Variant
aItems.Add q1
aItems.Add q2
aItems.Add q3
aItems.Add q4
aItems.Add q5
aItems.Add q6

Dim aScores As New Collection
Dim intCounter2 As Integer
Dim ctl1 As Variant
aScores.Add s1


aScores.Add s2
aScores.Add s3
aScores.Add s4
aScores.Add s5
aScores.Add s6

For Each ctlA In aItems
' For Each ctl1 In aScores
If ctlA.Value = "a" Then
ctl1.Value = "1"
Else: ctl1.Value = "0"
End If
' Next ctl1
Next ctlA

End Sub

[Robeen]

In your example, you have two lines of code commented out:

' For Each ctl1 In aScores
If ctlA.Value = "a" Then
ctl1.Value = "1"
Else: ctl1.Value = "0"
End If
' Next ctl1

Are trying to run it like this? If so, the For-Next of the inner loop is not going to work.

[Monica2]

No, I commented out as I was getting errors. No matter what I try Access does not seem to recognise the 2nd collection, so I don't think it even gets to the 2nd loop

[Robeen]

I ran your code.
ctlA never has a value.

I have to confess I have never used collections much, but I think that if your code never encounters "a" - then it will never get into your second collection.

I'm not sure if this will help - but this is what I ran when I was trying to understand your predicament:

 
For Each ctlA In aItems
MsgBox ctlA
Next ctlA

The MsgBox always returns a blank - which tells me there may be nothing there.

Where are you assigning the value "a" to ctlA?

[Robeen]

P.S. If you are able to figure it out, I'd like to know how you get it to work.
Thanks!

[Monica2]

Hi, appreciate the help. Values for q1 - q6 are entered by a user on a form. When the user clicks a score button on the form, Access must look up the value for q1 and then either assign a value of "1" or "0" to s1, then do the same for q2/s2, q3/s3 etc. Rest of code runs like a dream, just cannot get the 2nd collection and/or 2nd loop to run .

Will certainly let you know if (when?) I figure it out...

[Monica2]

Had to change my approach:

First set s1=q1, s2=q2 etc,
then created collection with s1-s6 and ran the loop changing all "a" values to "1" values,
then created new variables v1 - v6 (with s1 as number type it would not take the text values of q1 & as text type would not do the calculation!),
then set s1=v1, s2=v2 etc and
did calculations on v1 - v6.

Probably not the most elegant of coding, but it works!

[TinaCa]

My suggestion was going to be for a select case or maybe an array (which I hate doing).

Works it what counts!!

[rpeare]

Here's a copy of your database that I've modified. I don't know the makeup of your questions/answers. Perhaps you want to be able to alter the order of questions on the fly and the question text as time goes by but this database can handle that as well as keep track of the score. I'm not normally a fan of keeping calculated values in tables but without building a relatively complex system to determine which version of test/survey was active on a given date this is the easiest way to handle your data.