Cycle through variables incremented by a digit

I have an Access form where a bunch of sections are repeated, ex: Name, Age, Gender. The variable names for these text boxes are: Name1, Name2, ...; Age1, Age2, ...; etc.

I want to store all these values in an Array after a button is pressed. Instead of hardcoding all the variable names, I was wondering if there is a way to cycle through these in a for loop e.g.:

For i = 1 to 4
 ArrayName(i) = ("Name" & i).value 
 ArrayAge(i) = ("Age" & i).value
 ArrayGender(i) = ("Gender" & i).value
next i

I can get the string Name1 by ("Name" & i) but when I add .value after it, it doesn't seem to work.

I have also tried storing the variable name as a string into another array and then trying to use that array element to get the value, but that doesn't work either.

Solution 1:

The error is because code is just building a string "Name1" and string does not have Value property. Consider:

Me.Controls("Name" & i).Value

or simplify with:

Me("Name" & i)

Value is default property so don't have to reference. However, the Me qualifier is important, otherwise the array will just populate with the control's name "Name1", not the control's value.

Arrays are 0-base by default which means the element index begins with 0 so iterating 1 to 4 to reference array elements will not provide correct results. Consider:

ArrayName(i - 1) = Me("Name" & i)

Otherwise, declare arrays as 1-base with Option Base 1 in module header.

Instead of 3 1-dimension arrays, a single 2-dimension array might serve.

Dim aryD(4, 3) As Variant, x As Integer, y As Integer
For x = 1 To 4
    For y = 1 To 3
        aryD(x - 1, y - 1) = Me(Choose(y, "Name", "Age", "Gender") & x)
    Next
    'verify array elements values
    Debug.Print aryD(x - 1, 0) & " : " & aryD(x - 1, 1) & " : " & aryD(x - 1, 2)
Next