can I use Eval() for report controls ?

[Guest]

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?

 

[Duane Hookom]

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

 

[Guest]

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

 

[Marshall Barton]

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)

 

[Duane Hookom]

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.

 

[Guest]

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]

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.