Reference a control through a string?

[zswanson]

Hi all,

I have a sub-form that has 196 controls on it. The name for each control is a nubmer from 1 to 196.

I'd like to generate a loop that assigns a value to each control.

Is there a way to reference a control through a variable?

This is what I have now, without trying to attempt to reference through string:
(arrLayoutArray is a 28 row by 7 columns array that was generated in a seperate module)

 
        Me!sfmSubform.Form![1].Value = arrLayoutArray(1, 1)
        Me!sfmSubform.Form![2].Value = arrLayoutArray(1, 7)
        Me!sfmSubform.Form![3].Value = arrLayoutArray(1, 2)
        Me!sfmSubform.Form![4].Value = arrLayoutArray(1, 3)
        Me!sfmSubform.Form![5].Value = arrLayoutArray(1, 4)
        Me!sfmSubform.Form![6].Value = arrLayoutArray(1, 5)
        Me!sfmSubform.Form![7].Value = arrLayoutArray(1, 6)
 
 

I could do this 196 times, but I will be using this for a lot of variations and that isn't very practical. I tried doing this below:

    Dim counter As Variant
    Dim counter2 As Integer
 
    counter2 = 0
 
    For counter = 1 To 28
 
        Me!sfmSubform.Form![counter2].Value = arrLayoutArray(1, 1)
        counter2 = counter2 + 1
        Me!sfmSubform.Form![counter2].Value = arrLayoutArray(1, 7)
        counter2 = counter2 + 1
        Me!sfmSubform.Form![counter2].Value = arrLayoutArray(1, 2)
        counter2 = counter2 + 1
        Me!sfmSubform.Form![counter2].Value = arrLayoutArray(1, 3)
        counter2 = counter2 + 1
        Me!sfmSubform.Form![counter2].Value = arrLayoutArray(1, 4)
        counter2 = counter2 + 1
        Me!sfmSubform.Form![counter2].Value = arrLayoutArray(1, 5)
        counter2 = counter2 + 1
        Me!sfmSubform.Form![counter2].Value = arrLayoutArray(1, 6)
        counter2 = counter2 + 1
 
    Next
 
 

But I get an error because it's looking for counter2 for the name of the control, where I want it to look at the value. I also tried declaring counter2 as a string and variant and that didn't work either.

Please help!

[Killer42]

Considering that you refer to a "subform", this is in MS Access, right?

[Killer42]

Considering that you refer to a "subform", this is in MS Access, right?

I believe you can use the Controls collection. In your example, that could make your code something like this...

Dim c As Long
 
For c = 0 To 27 Step 7
  Me!sfmSubform.Form.Controls(Format$(c)).Value = arrLayoutArray(1, 1)
  Me!sfmSubform.Form.Controls(Format$(c + 1)).Value = arrLayoutArray(1, 7)
  Me!sfmSubform.Form.Controls(Format$(c + 2)).Value = arrLayoutArray(1, 2)
  Me!sfmSubform.Form.Controls(Format$(c + 3)).Value = arrLayoutArray(1, 3)
  Me!sfmSubform.Form.Controls(Format$(c + 4)).Value = arrLayoutArray(1, 4)
  Me!sfmSubform.Form.Controls(Format$(c + 5)).Value = arrLayoutArray(1, 5)
  Me!sfmSubform.Form.Controls(Format$(c + 6)).Value = arrLayoutArray(1, 6)
Next
 

My syntax may not be 100% right, but it should demonstrate the idea well enough. The form's Controls collection allows you to access all the controls by supplying the name as a string. However, there is something you need to be aware of. The controls in the collection can be accessed not only by name, but also by number (a sequential index number assigned automatically, presumably in the order they were created.)

So these two expressions would not return the same control, except by coincidence...

Controls(12)
Controls("12")

This is an unfortunate side-effect of using numbers as the names of your controls. And that's why I have used the old Format$ function, in an attempt to force VB to see the numbers as strings.

Hope you followed all that. I do tend to ramble on a bit.

P.S. I just changed counter to c to overcome some formatting problems in the message. TheScripts has a bit of a problem at the moment with certain long strings, and inserts weird spaces. This change avoided it.)

[zswanson]

Alright, i'll give that a try.

Thanks for your help

It worked! Thanks again

I will visit this forum more often now...

[Killer42]

Alright, i'll give that a try.

Thanks for your help

It worked! Thanks again

I will visit this forum more often now...

Glad to hear it. :)

Before you know it, you'll be answering other people's questions. That's how we all got hooked.