Control Arrays

[royhouser]

This is more of a "For Next time question" but, Does VBA in access support Control Arrays (i.e. txtName(1) through txtName(10))? If so how do you put them on the form. I know that I've worked with them before in either VB 6 or Viz C++, but didn't know if VBA in Access supported them. At this point I hope they don't because I've already designed and began coding on the form that has 520+ text boxes and 480 that all have to be coded for a "On Lost Focus" event to update the totals. It's an 8 tab form with 6 columns, 10 in each column. So I would've like to been able to set them up like txtValue(1)....(10) and so on....but noooooooo......thanx for any help.

royhouser

More is lost by Indecision than Wrong Decisions

 

[Reinier11]

Hi,
As far as I know there is no control collection in vba.
You can try something like:
dim c as control
dim i as long
dim strName as string
strName="myName"
for i = 0 to 100
strName=strname & cstr(i)
set c=me.controls(strName)

etc...

 

[royhouser]

that's what I've resorted to doing, that's why it was a more for next time question. I was just hoping that there was a shorter way, code wise. Mine looks like this currently:

'Stock Value SubTotals
intCt = 1
strType = "txtStockTax" & intCt
dblTotal = 0
dblValue = 0
Do While intCt <= 10
If Form.Controls(strType) <> &quot;&quot; Then
dblValue = Form.Controls(strType)
dblTotal = dblTotal + dblValue
End If
txtStockTaxTotal = dblTotal
intCt = intCt + 1
strType = &quot;txtStockTax&quot; & intCt
Loop

I have to repeat this code for each column on each tab....It's just overwhelming.

royhouser

More is lost by Indecision than Wrong Decisions

 

[Reinier11]

Well, sorry I don't have a complete solution, just a suggestion.
Maybe you could use the CreateControl method in a loop. This way you can easily set the properties of the controls, especially their names and the on_lost_focus event, which you set to your 'Stock Value SubTotals'-function.

Think it would work, but would have a problem myself assigning the controls to their specific tabs.
What do you think?

 

[royhouser]

I really appreciate your feedback. I hope that I didn't come of short earlier, it's just so frustrating having to code this way, arrays are SOOOOO much easier to deal with.

As far as assigning the text boxes to the specific tabs, you would just have to select the tab using:
TabCtl1.Pages(&quot;Stocks and Funds&quot.setfocus
Then use the CreateControl method.

As far as for my project, That would have worked, but I think that it would have been harder to create and assign the controls as you go. Currently, I have the controls set to disabled and not visible and on the lost focus of the current row of controls, if they are &quot;dirty&quot; then the next row becomes visible and enabled. I also have to update all of the totals on the lost focus of each control....(UGH). That's 480 seperate, yet nearly identical sets of code.....see what I mean &quot;FRUSTRATING&quot;

Anyway, Thanks again, maybe someday I'll find an easier way.

royhouser

More is lost by Indecision than Wrong Decisions

 

[TonyJollans]

Hi royhouser,

You can't have arrays in the way I think you're asking but I think you could have made your life a whole lot easier.

If all you want to do is run the same code for each of your textbox's lostfocus events, code it once and invoke it from a macro; then set each lostfocus event to the macro.

Inside your code you can refer to your controls in various ways - probably the easiest is to run through the whole collection and identify the ones you want by some criteria you have set (.Name Like &quot;txtStock*&quot; or a Tag you could give them all or whatever else suits).

Enjoy,
Tony

 

[royhouser]

Tony,
Thanx for the Feedback. I elected to stay away from macros because I wanted to make the code as easily transported as possible.
As far as the naming of the boxes, I've named all of the similar boxes with the same name and an incrementing last digit. (i.e. txtStock1, txtStock2, etc.)This way I only had to assign the name and a number to a string and then run the loop, turns out it wasn't half bad.

royhouser

More is lost by Indecision than Wrong Decisions