Looping through optiongroups to pick up option values. 2

[toptenor]

Help!

I have a form with eight optiongroups and want to loop through them to pick up the optionvalue of each so I can change their bordercolor - if an optionvalue >0 to black, and if no optionvalue (ie =0) to red so the user knows they must tick a box within that optiongroup.

The form is designed for children to look at an image and to assign an emotion to that image and then to say how sure they are of their choice (this for my wife's PhD work!).

The code I have written is as follows:

Private Sub optgrpWhiteGirl1_Click()
Dim ChangeableLabels As Variant
ChangeableLabels = OptionGroup


ChangeableLabels = Array("optgrpWhiteGirl1", "optgrpWhiteGirl1Sure", _
        "optgrpBlackGirl2", "optgrpBlackGirl2Sure", "optgrpWhiteBoy3", _
        "optgrpWhiteBoy3Sure", "optgrpBlackBoy4", "optgrpBlackBoy4Sure")

                For Each x In ChangeableLabels
                        If ChangeableLabels(x).OptionValue > 0 Then
                        ChangeableLabels(x).BorderColor = 0
                    ElseIf ChangeableLabels(x).OptionValue = 0 Then
                        ChangeableLabels(x).BorderColor = 255
                    End If
                Next

which I was going to call from each of the optiongroups when an "on_click" action takes place.

The code is hanging at the line "If ChangeableLabels(x).OptionValue > 0 Then" with the error message "Runtime Error 13, Type Mismatch".

I can't for the life of me understand why.

Can someone please help a relative newcomer to Access coding?

Thanks in advance.

 

[Golom]

You are using the "For" loop incorrectly. The syntax
[blue][tt]
For Each X In ChangeableLabels
[/tt][/blue]
Is designed to loop through the elements of a collection and "X" takes on the value of an element of the collection on each iteration of the loop. In this case "X" will have the value "optgrpWhiteGirl1" on the first iteration; "optgrpWhiteGirl1Sure" on the second; etc. You are getting "Type Mismatch" because those are not valid subscripts.

If you want to iterate through the array using the array subscript then you need a For statement like
[blue][tt]
For n = LBound(ChangeableLabels) to UBound(ChangeableLabels)
If ChangeableLabels.OptionValue > 0 Then
[/tt][/blue]

The other problem here is that your array (i.e. ChangeableLabels) contains text strings with the names of controls ... it does not contain the controls themselves. A reference to a control property such as "ChangeableLabels.OptionValue" is going to be invalid because the text string does not have an OptionValue property.

Just to illustrate, suppose I had a label on a form called "Label1" and then ran the following code
[blue][tt]
x = "Label1"
Debug.Print x.Caption
[/tt][/blue]
The system will respond with the error "Object Required" which means that, to specify an object property, I must use an object that has properties. "X", being a text string, does not.

 

[toptenor]

Many thanks for that. I can understand the part about the Lower Bound and Upper Bound, but (sorry to be so thick!) how do I get the code to "see" the controls so it can act on them?

 

[RoyVidar]

Hi!

I think this is possible using the

[tt]Me(VariableName).Value[/tt] - notation (using the Trim function, too, in case of leading/trailing spaces):

[tt]For n = LBound(ChangeableLabels) to UBound(ChangeableLabels)
If Me(Trim(ChangeableLabels)).Value > 0 Then
Me(Trim(ChangeableLabels)).BorderColor = 0
Else
Me(Trim(ChangeableLabels)).BorderColor = 255
End If
Next n [/tt]

Or use your original version:

[tt]For Each x In ChangeableLabels
If Me(Trim(x)).OptionValue > 0 Then
Me(Trim(x)).BorderColor = 0
ElseIf Me(Trim(x)).OptionValue = 0 Then
Me(Trim(x)).BorderColor = 255
End If
Next[/tt]

Roy-Vidar

 

[Golom]

Yeah ... I was afraid that you would ask that.

Try this
[blue][tt]
Dim ChangeableLabels() As OptionButton
Dim X As OptionButton

ChangeableLabels = Array(optgrpWhiteGirl1, optgrpWhiteGirl1Sure, _
optgrpBlackGirl2, optgrpBlackGirl2Sure, optgrpWhiteBoy3, _
optgrpWhiteBoy3Sure, optgrpBlackBoy4, optgrpBlackBoy4Sure)

For Each X In ChangeableLabels
X.BorderColor = IIF ( X.OptionValue > 0, 0, 255 )
Next
[/tt][/blue]
In the array assignment, the items in the array are the names of "OptionGroup" controls ... not the value of the "Name" property for those controls. (Note that they do not have quotes around them.)

 

[toptenor]

Thanks Golom and Roy,

Unfortunately neither of your suggested methods are working!
Golom's is nearest (sorry Roy) as now the entries in the array are showing values when I mouse over them in debug mode. But despite trying all sorts, I cannot get those values to work in the "If, Then, Else statement" - I keep getting the "Type Mismatch" error message.

As I said before this thing is driving me mad!!

 

[Golom]

OK. Try this
[blue][tt]
Dim ChangeableLabels() As OptionButton
Dim X As OptionButton
Dim n As Integer

ChangeableLabels = Array(optgrpWhiteGirl1, optgrpWhiteGirl1Sure, _
optgrpBlackGirl2, optgrpBlackGirl2Sure, optgrpWhiteBoy3, _
optgrpWhiteBoy3Sure, optgrpBlackBoy4, optgrpBlackBoy4Sure)

For n = LBound(ChangeableLabels) To UBound(ChangeableLabels)
Set X = ChangeableLabels
X.BorderColor = IIF ( X.OptionValue > 0, 0, 255 )
Next
[/tt][/blue]

 

[toptenor]

That is still breaking on the line "ChangeableLabels = Array(optgrpWhiteGirl1, ..."with an error message of "Type Mismatch" (Runtime error 13)

Can you begin to see the problems I've had with this!!!

 

[toptenor]

Looking at this more closely, when I mouse over the entries in the Array in debug mode, they show entires like "optgrpWhiteGirl1 = 4" where the last numeral (in this case the "4" is the optionvalue that is in the optiongroup on the form.
How then do I assign that "4" into the "If, Then" statement to make that work? Can I use RTrim function to pass that value?

 

[Golom]

OK. This is somewhat pedestrian but it does work in a test I ran
[blue][tt]
Dim ChangeableLabels() As OptionButton
Dim n As Integer
ReDim ChangeableLabels(7)
Set ChangeableLabels(0) = optgrpWhiteGirl1
Set ChangeableLabels(1) = optgrpWhiteGirl1Sure
Set ChangeableLabels(2) = optgrpBlackGirl2
Set ChangeableLabels(3) = optgrpBlackGirl2Sure
Set ChangeableLabels(4) = optgrpWhiteBoy3
Set ChangeableLabels(5) = optgrpWhiteBoy3Sure
Set ChangeableLabels(6) = optgrpBlackBoy4
Set ChangeableLabels(7) = optgrpBlackBoy4Sure

For n = LBound(ChangeableLabels) To UBound(ChangeableLabels)
ChangeableLabels.BorderColor = IIf(ChangeableLabels.OptionValue > 0, 0, 255)
Next
[/tt][/blue]

 

[toptenor]

Thats it! Many, many thanks.

It worked when I set the forst Dim statement to "=OptionGroup" rather than "OptionButton" and I had to take out the ".OptionValue" in the IIf statement. (See my previous comments about the array returning option values)

I am now off to bed and won't be waking at 3 in the morning with this thing chugging around my febrile (but unfortunately not fertile) brain!