Using Controls within a For...Next loop

FalseParadigm · December 19th, 2006, 02:23 PM

I have ran into an interesting situation. Using a Form, the user will enter up to 20 records at a time (like an order form where there will be 20 part numbers, 20 descriptions, 20 unit proces, etc). I named them all with the same convention: txtQty0, txtQty1, txtQty2, and so on.

Because there is some math involved, I have arrays for the data to avoid writing the same formula (which is quite large and complex) over and over. However, I found that I need to reference the values of the controls themselves in my formula.

Is there a way to handle controls in the same way as other variables (such as arrays) so that I can use them in my For...Next loops? I would love to do something like this:

Code:

Dim x As Integer
Dim aQty(19) As Integer

For x = 0 to 19
   aQty(x) = txtQty(x).Value
Next x

But I know that this isn't possible. Does anyone know if there is a another way to pull this off?

I tried to explain what I'm trying to do as best as I could, so if anyone needs any clarification, please let me know.

--------------------------------
Ben

"It's my way, or the Hemingway."
--------------------------------

leehambly · December 20th, 2006, 05:42 AM

Yeah it is possible...

You need to build the control name in a string and then identify the control using the string, so something like...

strTempControlName = txtQty & x
aQty(x) = me.Controls(strTempControlName).Value

to replace your line

aQty(x) = txtQty(x).Value

Should do the trick, I think.

FalseParadigm · December 20th, 2006, 09:55 AM

Thank you, this works perfectly. I was unaware that Me.Controls could be used in such a way. This will certainly help me out in other areas of my app. Thank you again. Its like an early Christmas present!

--------------------------------
Ben

"It's my way, or the Hemingway."
--------------------------------

leehambly · December 20th, 2006, 10:01 AM

This is especially useful in reporting, you can build control arrays and really do some on-the-fly formatting... Merry Xmas!