Parameterizing field names

[Guest]

I would like to be able to declare the fields on a form as an array, so
allowing me to address them as ‘Field(n)’, for example. This would allow me
to allocate values to the (unbound) fields, in the form’s Open sub-routine,
using a structure of the type:-

For J=1 To N
Field(J)= Calculate_Value(J)
Next

The alternative - allocating individual values - can result in what seems to
be unnecessarily prolix code in the case of a form with a significant number
of fields. I can think of one or two ways of trying to get around this but
they are not very elegant. Can anyone suggest how to set up a system of the
above, or similar, type?

 

[Albert D. Kallal]

there is already a pre-built array of contorls for you on each form...


dim mycontorl as varient


for each mycontorl in me.contorls

next mycontorl..

or,

for j = 0 to me.contorls.count -1

contorls(j)

next j

 

[Marshall Barton]

I would like to be able to declare the fields on a form as an array, so
allowing me to address them as ‘Field(n)’, for example. This would allow me
to allocate values to the (unbound) fields, in the form’s Open sub-routine,
using a structure of the type:-

For J=1 To N
Field(J)= Calculate_Value(J)
Next

The alternative - allocating individual values - can result in what seems to
be unnecessarily prolix code in the case of a form with a significant number
of fields. I can think of one or two ways of trying to get around this but
they are not very elegant. Can anyone suggest how to set up a system of the
above, or similar, type?

You are talking about **controls**, right?

If so, use the syntax:
Me("abc" & J) = calc(J)

 

[Guest]

Thank you for your help. I did not previously realize that Me(“abc†& J) was
valid syntax. I have not seen that before. (“You learn something new every
dayâ€, as they say.) I tried Me![“abc†& J] or even Me.stConcat, preceded by
the statement stConcat=“abc†& J, together with variations thereof, but was
not really surprised when these were rejected. However, thanks to your
input, I have now got the thing working well and have managed to dispense
with a lot of redundant verbiage in the process.

 

[Guest]

Thanks for your assistance but I am afraid that I was unable to relate the
form controls to the Controls property.

The form has eleven unbound controls (or fields, as I called them
previously) named T1, T2 … T11 but I clearly do not understand how these
controls are indexed. Is it the same as the tab order? The compiler would
only recognize Controls(2) but, although it appeared to allocate a calculated
value to it, nothing appeared in T2, on the form, or in any other T control,
for that matter. Any index other than 2 resulted in the run-time error,
“Object does not support this property or method.†Later investigation
suggested that Controls(2) actually relates to an unrelated option group that
shares the form with the T1…T11 group so it still remains a mystery as to how
I could access or set the contents of the latter using the Controls property.
If you were able to enlighten me, I would be most grateful. How, for
example, do I find the Controls index for, say, T2, which has a tab index of
2? It clearly is not Controls(2).

Marshall’s suggestion worked well in this case but I might encounter other
situations where it is not so convenient to synthesize control names in this
way, thus making direct access by means of the Controls indices a more
convenient approach.

I hope you will understand that I have rated your response negatively,
simply because it did not answer my question - but I suspect that that is
more my problem than yours.

 

[Albert D. Kallal]

You need to try the following:

msgbox controls.count

Howe many do you get for the above code (just place it behind a test button
on your form).

Further..try:

dim i as integer

for i = 0 to contorls.Count -1
debug.print contorls(i).name
next i

run the above code...and see what you get in the debug window (place the
above code behind the button...run it..and then look at the debug window to
see the results....

 

[Guest]

I am sorry that it took me a few days to get around to running your
suggestions but, when I did, your code threw much light on the matter. There
were a lot more controls on the form than I had realised! I was looking in
the wrong place.

Unfortunately, despite providing a mechanism for identifying controls, the
results of the exercise threw up a snag. As you are undoubtedly well aware,
the order in which controls are added to a form determines their indices.
Consequently, as long as a set of controls is added sequentially, or in some
sort of regular order, it is only necessary to find the index of the first
member in order to access the whole set, with, for example, a For…Next loop.
However, if additional members of the set are introduced later – such as
adding an option button to an option group – then sequential access to the
members of the group, by means of their indices, can become a lot more messy.

Is it possible to manually re-index the controls on a form, so enabling a
non-sequentially indexed group to be reordered into sequential order – like,
say, reorganizing the tab order? If not, then the only alternative I can see
is to rebuild the form from scratch, re-adding the controls in a controlled
order – and that’s not a particularly attractive solution.

 

[Marshall Barton]

I am sorry that it took me a few days to get around to running your
suggestions but, when I did, your code threw much light on the matter. There
were a lot more controls on the form than I had realised! I was looking in
the wrong place.

Unfortunately, despite providing a mechanism for identifying controls, the
results of the exercise threw up a snag. As you are undoubtedly well aware,
the order in which controls are added to a form determines their indices.
Consequently, as long as a set of controls is added sequentially, or in some
sort of regular order, it is only necessary to find the index of the first
member in order to access the whole set, with, for example, a For…Next loop.
However, if additional members of the set are introduced later – such as
adding an option button to an option group – then sequential access to the
members of the group, by means of their indices, can become a lot more messy.

Is it possible to manually re-index the controls on a form, so enabling a
non-sequentially indexed group to be reordered into sequential order – like,
say, reorganizing the tab order? If not, then the only alternative I can see
is to rebuild the form from scratch, re-adding the controls in a controlled
order – and that’s not a particularly attractive solution.

Not only is that not attractive, it's not fool proof. The
controls collection's index order is not a documented
feature and can not be relied on from one version of Access
to another (maybe not even in the same version and some of
the OCX controls).

I stronly suggest that you give up this quest and use
documented features to accomplish an equivalent capability.
You can create a private collection of controls (populated
in the form's Open or Load event) with any indexing scheme
you like. Another, slightly clumsier, approach would be to
put something in the Tag property of the controls that would
indicate the group and index within the group.

 

[Albert D. Kallal]

Is it possible to manually re-index the controls on a form, so enabling a

non-sequentially indexed group to be reordered into sequential order -
like,
say, reorganizing the tab order? If not, then the only alternative I can
see
is to rebuild the form from scratch, re-adding the controls in a
controlled
order - and that's not a particularly attractive solution.

Actually, not really, but then again if we "can" come up with a solution
that does care about order, then we kind of hit the jack pot.?

If you really have 4, or 7 controls out of a whole bunch, then I suggest to
use the "tag" property of those controls.

Assuming 50 controls, but we only want to deal with a set of our 7 controls,
then you simply place some identifiable text in the tag.

For example, We could place the text

Group1

in the tag property.

then we go:

if mycontorl.Tag = "group1" then
do something

Thus, we can have groups of controls. And, you can even build a custom
collection of controls.

The "tag" property of a control can be found on the "other" tab of a control
(and, note that you can highly 4, or 15 controls on a form,a nd then type in
he name for the tag of this group).

Hence:
We could define a form level collection.

eg:

Option Compare Database
Option Explicit

Dim colContorls As New Collection


Now, in the forms on-load, we go:

Dim c As Control
For Each c In Controls
If c.Tag = "Group1" Then
colControls.Add c, c.Name
End If
Next c


Now, we have a list of controls call colMyContorlList.

note that this collection is 1 based, and not zero based. (starts at one).

So, you could in theory make 2, or 3 sets of controls on a form,a nd deal
with them which every way you want.

For example. to make the "group1" controls all disabled....

for i = 1 to colControls.Count
colContorlList(i).enabled = false
next i

so, you don't actually have to make the extra code to move the control list
to a custom collection, but it can make things more easy..

And, of course the idea of using a naming convention for the controls as
suggested is also quite common....

So, there are several approaches to grouping controls on a screen...

 

[Guest]

Albert,

Thanks to you and Marshall, I now see the whole thing much more clearly.

I have a group of unbound controls named T1 to T12 and a second group, named
B1 to B12. The difference between the two groups is that the ‘T’ group is
contiguous whereas the ‘B’ group lacks controls B5 to B8, inclusive. My
first thought was that I could populate both groups in a single For…Next loop
by accessing their control indices. The message that has come back, however,
is, “Forget it!â€, and I now understand why.

I was seeking to access the controls using absolute addressing because I
could not initially find a way of using symbolic addressing. My attempts to
concatenate ‘T’ and ‘n’, where ‘n’ is a numerical digit, and then persuading
the VBA compiler to recognize ‘Tn’ as a valid name, all ended in failure.
Marshall, however, kindly pointed out a syntax that enables that approach to
be used and I have therefore concluded that it would be better, wherever
possible, to exploit this technique, rather than pursue absolute, or indexed,
addressing which, it would increasingly seem, is fraught with difficulties.
(I am, in any case, a lazy, rather than an adventurous, programmer. Thank
you both for enabling me to remain so.)