How to create an array of labels?

[Richard Hollenbeck]

Hello,

In regular VB, I remember I could create a generic label (call it something
like lblFieldNames) and copy it and paste it. VB would say that I already
have an object by that name then ask me if I wanted to create an array. So
I would end up with lblFieldNames(0) to lblFieldNames(n). Then I could
cycle through the QueryDef and fill the captions of the labels with the
correct field names. Is that possible in VBA, and more specifically, in an
Access report?

Rich Hollenbeck
Moreno Valley, CA

[Neil]

Access doesn't support control arrays like VB does. However, you can
manually name your controls in such a way that you can still loop through
them in code.

Say your control is called MyControl. Simply create a MyControl1,
MyControl2, MyControl3, etc. Then, when you want to loop through the
controls, do something like this:

For i = 1 to N
Me.Controls("MyControl" & i).Caption = "whatever"
Next

where N = the highest numbered control.

Of course, if you're not in the code module of the form or report you're
modifying, you can replace ME in the above with Forms!MyForm or
Reports!MyReport, or whatever your form or report name is.

HTH,

Neil

"Richard Hollenbeck" <ri****************@verizon.netwrote in message
news:ueObi.5628$c45.3477@trndny06...

Hello,

In regular VB, I remember I could create a generic label (call it
something like lblFieldNames) and copy it and paste it. VB would say that
I already have an object by that name then ask me if I wanted to create an
array. So I would end up with lblFieldNames(0) to lblFieldNames(n). Then
I could cycle through the QueryDef and fill the captions of the labels
with the correct field names. Is that possible in VBA, and more
specifically, in an Access report?

Rich Hollenbeck
Moreno Valley, CA

[Richard Hollenbeck]

That works! Thanks.

"Neil" <no****@nospam.netwrote in message
news:fr******************@newssvr14.news.prodigy.n et...

Access doesn't support control arrays like VB does. However, you can
manually name your controls in such a way that you can still loop through
them in code.

Say your control is called MyControl. Simply create a MyControl1,
MyControl2, MyControl3, etc. Then, when you want to loop through the
controls, do something like this:

For i = 1 to N
Me.Controls("MyControl" & i).Caption = "whatever"
Next

where N = the highest numbered control.

Of course, if you're not in the code module of the form or report you're
modifying, you can replace ME in the above with Forms!MyForm or
Reports!MyReport, or whatever your form or report name is.

HTH,

Neil

"Richard Hollenbeck" <ri****************@verizon.netwrote in message
news:ueObi.5628$c45.3477@trndny06...

>Hello,

In regular VB, I remember I could create a generic label (call it
something like lblFieldNames) and copy it and paste it. VB would say
that I already have an object by that name then ask me if I wanted to
create an array. So I would end up with lblFieldNames(0) to
lblFieldNames(n). Then I could cycle through the QueryDef and fill the
captions of the labels with the correct field names. Is that possible in
VBA, and more specifically, in an Access report?

Rich Hollenbeck
Moreno Valley, CA

[Neil]

Great! Of course, you can always just create an array in code that holds the
names of the controls you want to modify, and then name them whatever you
want. That would work as well, but it's more work to set up.

Neil
"Richard Hollenbeck" <ri****************@verizon.netwrote in message
news:lvUbi.8307$yS4.3216@trnddc04...

That works! Thanks.

"Neil" <no****@nospam.netwrote in message
news:fr******************@newssvr14.news.prodigy.n et...

>Access doesn't support control arrays like VB does. However, you can
manually name your controls in such a way that you can still loop through
them in code.

Say your control is called MyControl. Simply create a MyControl1,
MyControl2, MyControl3, etc. Then, when you want to loop through the
controls, do something like this:

For i = 1 to N
Me.Controls("MyControl" & i).Caption = "whatever"
Next

where N = the highest numbered control.

Of course, if you're not in the code module of the form or report you're
modifying, you can replace ME in the above with Forms!MyForm or
Reports!MyReport, or whatever your form or report name is.

HTH,

Neil

"Richard Hollenbeck" <ri****************@verizon.netwrote in message
news:ueObi.5628$c45.3477@trndny06...

>>Hello,

In regular VB, I remember I could create a generic label (call it
something like lblFieldNames) and copy it and paste it. VB would say
that I already have an object by that name then ask me if I wanted to
create an array. So I would end up with lblFieldNames(0) to
lblFieldNames(n). Then I could cycle through the QueryDef and fill the
captions of the labels with the correct field names. Is that possible
in VBA, and more specifically, in an Access report?

Rich Hollenbeck
Moreno Valley, CA