I have a report with 50 columns whch must have their background colors set
based on values in an input table. Can I use Eval within a loop to do this?
All attempts so far have failed. Or is there another way to loop thru the
controls in a report section?

What are you attempting to do? You can loop through the controls collection
of a report fairly easily.

Dim ctl as Control
For each ctl in Me.Controls
'some code
Next

--
Duane Hookom
MS Access MVP
--

"mscertified" <(E-Mail Removed)> wrote in message
news:AED738FF-1F94-4B77-AEC2-(E-Mail Removed)...
>I have a report with 50 columns whch must have their background colors set
> based on values in an input table. Can I use Eval within a loop to do
> this?
> All attempts so far have failed. Or is there another way to loop thru the
> controls in a report section?

I have heading controls H1 thru H50 in my page header and I need to
programatically set the values where H1 is the number of the current month,
then incrementing from there eg 8,9,10,11,12,1,2,3 etc.
I'm using your method to get the control names but how do I set the value. I
tried
ctl =
ctl.value =
Eval(ctl.name) =

nothing seems to work.....

The other thing I need to do is to dynamically set the background colors of
each of 50 columns (named M1 thru M50) in the detail section based on numeric
values in my source table. E.g. M1=1 make background purple, M1=2 make
background red etc. etc. The colors will vary from column to column and row
to row. I hope I don't have to use conditional formatting for this as I need
more than 5 variations.

any advice most appreciated

David

"Duane Hookom" wrote:

> What are you attempting to do? You can loop through the controls collection
> of a report fairly easily.
>
> Dim ctl as Control
> For each ctl in Me.Controls
> 'some code
> Next
>
> --
> Duane Hookom
> MS Access MVP
> --
>
> "mscertified" <(E-Mail Removed)> wrote in message
> news:AED738FF-1F94-4B77-AEC2-(E-Mail Removed)...
> >I have a report with 50 columns whch must have their background colors set
> > based on values in an input table. Can I use Eval within a loop to do
> > this?
> > All attempts so far have failed. Or is there another way to loop thru the
> > controls in a report section?
>
>
>

mscertified wrote:

>I have a report with 50 columns whch must have their background colors set
>based on values in an input table. Can I use Eval within a loop to do this?
>All attempts so far have failed. Or is there another way to loop thru the
>controls in a report section?


No. Eval is intended for a different purpose.

If your controls are named with a common prefix and a
numeric suffix, you can reference the controls using the
alternate syntax:

Me("prefix" & number)

--
Marsh
MVP [MS Access]

Since your heading controls are numbered, you should be able to set values
like:

Dim intCnt as Integer
For intCnt = 1 to 50
Me("H" & intCnt) = ...some value expression...
Next

This assumes your controls are unbound text boxes.

I think you might be making this harder than need be but you haven't really
provided much background.

--
Duane Hookom
MS Access MVP
--

"mscertified" <(E-Mail Removed)> wrote in message
news9F4CEE2-5907-4C30-A07C-(E-Mail Removed)...
>I have heading controls H1 thru H50 in my page header and I need to
> programatically set the values where H1 is the number of the current
> month,
> then incrementing from there eg 8,9,10,11,12,1,2,3 etc.
> I'm using your method to get the control names but how do I set the value.
> I
> tried
> ctl =
> ctl.value =
> Eval(ctl.name) =
>
> nothing seems to work.....
>
> The other thing I need to do is to dynamically set the background colors
> of
> each of 50 columns (named M1 thru M50) in the detail section based on
> numeric
> values in my source table. E.g. M1=1 make background purple, M1=2 make
> background red etc. etc. The colors will vary from column to column and
> row
> to row. I hope I don't have to use conditional formatting for this as I
> need
> more than 5 variations.
>
> any advice most appreciated
>
> David
>
> "Duane Hookom" wrote:
>
>> What are you attempting to do? You can loop through the controls
>> collection
>> of a report fairly easily.
>>
>> Dim ctl as Control
>> For each ctl in Me.Controls
>> 'some code
>> Next
>>
>> --
>> Duane Hookom
>> MS Access MVP
>> --
>>
>> "mscertified" <(E-Mail Removed)> wrote in message
>> news:AED738FF-1F94-4B77-AEC2-(E-Mail Removed)...
>> >I have a report with 50 columns whch must have their background colors
>> >set
>> > based on values in an input table. Can I use Eval within a loop to do
>> > this?
>> > All attempts so far have failed. Or is there another way to loop thru
>> > the
>> > controls in a report section?
>>
>>
>>

My 1600 page Access book does not mention this syntax, but I'll give it a try.
Can I set control properties that way too, e.g. Background color?

David

"Marshall Barton" wrote:

> mscertified wrote:
>
> >I have a report with 50 columns whch must have their background colors set
> >based on values in an input table. Can I use Eval within a loop to do this?
> >All attempts so far have failed. Or is there another way to loop thru the
> >controls in a report section?
>
>
> No. Eval is intended for a different purpose.
>
> If your controls are named with a common prefix and a
> numeric suffix, you can reference the controls using the
> alternate syntax:
>
> Me("prefix" & number)
>
> --
> Marsh
> MVP [MS Access]
>

Yes, you can reference an element of any collection using
that syntax (see Collection and Item in Help).

The Properties collection is no different. e.g.
strPName = "BackColor"
Me.sometextbox.Properties(strPName) = somevalue

The reason you do not need to specify the Controls
collection when you use Me(strControlName) is that Controls
is the default collection for a form object. The full
rigorous syntax is:
Me.Controls(strControlName)
well, actually it's
Me.Controls.Item(strControlName)
but hardly anyone explicitly uses the Item property.
--
Marsh
MVP [MS Access]



mscertified wrote:
>My 1600 page Access book does not mention this syntax, but I'll give it a try.
>Can I set control properties that way too, e.g. Background color?
>
>
>> mscertified wrote:
>> >I have a report with 50 columns whch must have their background colors set
>> >based on values in an input table. Can I use Eval within a loop to do this?
>> >All attempts so far have failed. Or is there another way to loop thru the
>> >controls in a report section?
>>
>>
>"Marshall Barton" wrote:
>> No. Eval is intended for a different purpose.
>>
>> If your controls are named with a common prefix and a
>> numeric suffix, you can reference the controls using the
>> alternate syntax:
>>
>> Me("prefix" & number)