Problem with the Form TextBox value format

[mabrynda]

Dear All,
I have the following problem. In a form, I have several text boxes, from which I have to collect the numerical values introduced by the user. I can do that assigning a variable to each text box and then typing many lines looking like:

Variable1 = ([Forms]![FormName]![txtBoxName1])
Variable2 = ([Forms]![FormName]![txtBoxName2])
Etc...

This works fine.
However, I would like to reduce the number of lines in my code and decided to use an array to stock this information, especially because the txtBox names are a logical sequence. Therefore I have a loop, in which I'm trying to fill the array with the numerical values obtained from the txtBoxes. The problem is that I can't find the appropriate format to express the txtBoxName by incrementing it with the increasing numbers (such as txtBoxName1, txtBoxName2, txtBoxName3, etc...). The expression Variable = ([Forms]![FormName]![txtBoxName]) doesn't seem to accept dynamic names within it. Anybody has an idea ho to resolve this problem?
Thank you in advance.

Marcin

Here is the code that gives me the error (the program does not recognize " & i & " as an incrementing variable, but rather takes it as full text.

 
Dim ValArrayDyn_B(2, 8) As Double
Dim ValArrayDyn_C(2, 8) As Double
 
For i = 1 To 2
For k = 1 To 8
 
 
ValArrayDyn_B(i, k) = ([Forms]![frmLloydsViewsDyn3MTab1]![ValBS" & i & "C" & k & "_B])
Debug.Print "ArrayValue", ValArrayDyn_B(i, k)
ValArrayDyn_C(i, k) = ([Forms]![frmLloydsViewsDyn3MTab1]![ValBS" & i & "C" & k & "_C])
Debug.Print "ArrayValue", ValArrayDyn_C(i, k)
 
 
Next k
Next i
 
 

[MikeTheBike]

@mabrynda
Hi

You could try something like this

Dim ValArrayDyn_B(2, 8) As Double
Dim ValArrayDyn_C(2, 8) As Double
 
For i = 1 To 2
    For k = 1 To 8
        ValArrayDyn_B(i, k) = Me.Controls("ValBS" & i & "C" & k & "_B")
        Debug.Print "ArrayValue", ValArrayDyn_B(i, k)
        ValArrayDyn_C(i, k) = Me.Controls("ValBS" & i & "C" & k & "_C")
        Debug.Print "ArrayValue", ValArrayDyn_C(i, k)
    Next k
Next i

??

To use concatenation you must be assigning the result to a string, which in your case (as far as the interpreter is concerned) it isn’t.

However, the argument for the Controls collection object is a string, so this should work.


MTB

[mabrynda]

Hi MTB,
Thanks so much!!! It works great. I didn't in fact realize I wasn't working with the string in this case.
Anyway, it shortens things a lot....

Marcin

[NeoPa]

Here's an alternative. Not better necessarily, but illustrates an alternative approach :

Dim intI As Integer, intJ As Integer
Dim ctl As Control
Dim ValArrayDyn_B(2, 8) As Double 
Dim ValArrayDyn_C(2, 8) As Double
 
For Each ctl In Me.Controls
    With ctl
        If .ControlType = acTextBox _
        And Left(.Name, 5) = "ValBS" Then
            intI = Val(Mid(.Name, 6, 1))
            intJ = Val(Mid(.Name, 8, 1))
            If Right(.Name, 2) = "_B" Then
                ValArrayDyn_B(intI, intK) = .Value
            Else
                ValArrayDyn_C(intI, intK) = .Value
            End If
        End If
    End With
Next ctl

[mabrynda]

@NeoPa
Dear NeoPa,
Thanks too for the alternative approach. There is still lot of things I need to learn in VBA, but slowly I'm approaching the level of expertise needed to work on my (mostly simple) projects.

Thanks again.

M.