By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,688 Members | 1,715 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,688 IT Pros & Developers. It's quick & easy.

Problem with the Form TextBox value format

P: 24
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim ValArrayDyn_B(2, 8) As Double
  3. Dim ValArrayDyn_C(2, 8) As Double
  4.  
  5. For i = 1 To 2
  6. For k = 1 To 8
  7.  
  8.  
  9. ValArrayDyn_B(i, k) = ([Forms]![frmLloydsViewsDyn3MTab1]![ValBS" & i & "C" & k & "_B])
  10. Debug.Print "ArrayValue", ValArrayDyn_B(i, k)
  11. ValArrayDyn_C(i, k) = ([Forms]![frmLloydsViewsDyn3MTab1]![ValBS" & i & "C" & k & "_C])
  12. Debug.Print "ArrayValue", ValArrayDyn_C(i, k)
  13.  
  14.  
  15. Next k
  16. Next i
  17.  
  18.  
Jul 1 '10 #1

✓ answered by MikeTheBike

@mabrynda
Hi

You could try something like this
Expand|Select|Wrap|Line Numbers
  1. Dim ValArrayDyn_B(2, 8) As Double
  2. Dim ValArrayDyn_C(2, 8) As Double
  3.  
  4. For i = 1 To 2
  5.     For k = 1 To 8
  6.         ValArrayDyn_B(i, k) = Me.Controls("ValBS" & i & "C" & k & "_B")
  7.         Debug.Print "ArrayValue", ValArrayDyn_B(i, k)
  8.         ValArrayDyn_C(i, k) = Me.Controls("ValBS" & i & "C" & k & "_C")
  9.         Debug.Print "ArrayValue", ValArrayDyn_C(i, k)
  10.     Next k
  11. 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

Share this Question
Share on Google+
4 Replies


Expert 100+
P: 636
@mabrynda
Hi

You could try something like this
Expand|Select|Wrap|Line Numbers
  1. Dim ValArrayDyn_B(2, 8) As Double
  2. Dim ValArrayDyn_C(2, 8) As Double
  3.  
  4. For i = 1 To 2
  5.     For k = 1 To 8
  6.         ValArrayDyn_B(i, k) = Me.Controls("ValBS" & i & "C" & k & "_B")
  7.         Debug.Print "ArrayValue", ValArrayDyn_B(i, k)
  8.         ValArrayDyn_C(i, k) = Me.Controls("ValBS" & i & "C" & k & "_C")
  9.         Debug.Print "ArrayValue", ValArrayDyn_C(i, k)
  10.     Next k
  11. 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
Jul 1 '10 #2

P: 24
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
Jul 1 '10 #3

NeoPa
Expert Mod 15k+
P: 31,769
Here's an alternative. Not better necessarily, but illustrates an alternative approach :
Expand|Select|Wrap|Line Numbers
  1. Dim intI As Integer, intJ As Integer
  2. Dim ctl As Control
  3. Dim ValArrayDyn_B(2, 8) As Double 
  4. Dim ValArrayDyn_C(2, 8) As Double
  5.  
  6. For Each ctl In Me.Controls
  7.     With ctl
  8.         If .ControlType = acTextBox _
  9.         And Left(.Name, 5) = "ValBS" Then
  10.             intI = Val(Mid(.Name, 6, 1))
  11.             intJ = Val(Mid(.Name, 8, 1))
  12.             If Right(.Name, 2) = "_B" Then
  13.                 ValArrayDyn_B(intI, intK) = .Value
  14.             Else
  15.                 ValArrayDyn_C(intI, intK) = .Value
  16.             End If
  17.         End If
  18.     End With
  19. Next ctl
Jul 1 '10 #4

P: 24
@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.
Jul 1 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.