Setting variables within a For Loop

[ScardyBob]

Hello All!

I was wondering if it is possible set an object to a variable within a
For.. Next... Loop. For example:

(This doesn't work but is essentially what I want to do)
i = 0
For Each ctrl In frm.Controls
If ctrl.ControlType = acSubform Then
Set subctrl & i = ctrl
i = i + 1
End If
Next ctrl

The main idea of this is to loop through all the controls, selecting
only the subform controls, and assigning them to incremented variable
(subctrl1, subctrl2, subctrl3, etc.) via the Set statement within the
For... Next... Loop. Is this, or something like it, possible? Or do I
have to set each variable manually.

Thanks in Advance!

Mike

 

[Michel Walsh]

Hi,


a computed expression cannot be on the left side of an assignment ( = ).

Try something like:


Dim mySubSet( ) As Controls
Redim mySubSet(0 to 755) As Controls
i = 0
For Each ctrl In frm.Controls
If ctrl.ControlType = acSubform Then
Set mySubSet( i ) = ctrl
i = i + 1
End If
Next ctrl

if (i>0) then
Redim Preserve mySubSet(0 to i) AS Controls
else
mySubSet=nothing
end if





Hoping it may help,
Vanderghast, Access MVP

 

[ScardyBob]

Michel,

After some trial and error I got it to work. Thanks for the help!
Though I do have a curiosity question. I kept getting a 'Type Mismatch'
error on the line 'Set mySubSet(i) = ctrl' which I solved by changing
mySubSet() from Controls to Control. It seems to me that mySubSet()
should be of type Controls as a collection of single Control's. Why do
I have to Dim mySubSet() as a Control rather then Controls?

The final code is:

Dim mySubSet() As Control
Dim ctrl As Control
Dim frm as Form
Dim i As Integer

ReDim mySubSet(0 To 755)
Set frm = Forms!frmDATA

i = 0
For Each ctrl In frm.Controls
If ctrl.ControlType = acSubform Then
ReDim Preserve mySubSet(0 To i)
Set mySubSet(i) = ctrl
i = i + 1
End If
Next ctrl

 

[Michel Walsh]

Hi,


Indeed, I used the plural because I use 756 of them (by instinct), but
Controls is a collection, Control is the element... and you must use
Control, even if you DIM 756 of them.


If you redim preserve at each "i" value, that may be a little bit slow, is
it perceptible? if not, leave it as it is.



Vanderghast, Access MVP

 

[ScardyBob]

Hi Michel,

Right now I'm only using this to set variables for 4 subform controls,
so there isn't really any noticable difference. I plan to add more
subform controls in the future so I may move the Redim Preserve outside
of the For Loop if it slows it down.

Thanks for the help!
Mike