Setting form Field Properties with code

[KM]

I have a lot of fields on a form/Subform
I wish to change the properties of some of the items via code such as this:

ME![CS1101].ControlSource = "=([11001] - [11004])"
ME![CS1102].ControlSource = "=([11001] - [11005])"

I have 4 fields similar where [CS1101] = Qtr 1 ...[CS1104] = Qtr 4

Question:
Is there anyway to refer to ME![CS1101].ControlSource indirectly with a
counter?
Where QTR = 1 to 4
ie: ME![CS110" & QTR & "].ControlSource = "=([11001] - [11005])"

Intent is to code 1 set of variables and let a do loop set the others...

 

[tina]

try

Dim i As Integer

For i = 1 to 4
Me.Controls("CS110" & i).ControlSource = ....
Next

though i'm not sure why you're setting the ControlSource to an expression.
why not just do the calculation in VBA and set the value of the control
instead? for example, assuming that ([11001] - [11004]) are referring to
fieldnames, or controlnames, in the form, and you're subtracting the value
of the second from the value of the first, try

Me.Controls("CS110" & i) = Me![11001] - Me![11004]

and, btw, if you have fields in your table named for quarter1, quarter2,
quarter3, and quarter4, then it's almost a given that your table isn't
normalized. recommend you read up/more on relational design principles, with
an eye to correcting your table(s) structure before proceeding. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Allen Browne]

To use a string for the control name, use:
Me.Controls("SomeControl")
Since Controls is the default collection, you can shorten this to:
Me.Controls("SomeControl")

So, this kind of thing:
Dim strField As String
For i = 1 to 4
strField = "CS" & (1000+i)
Me(strField).ControlSource = ...
Next

 

[KM]

This looks good! Thanks

To use a string for the control name, use:
Me.Controls("SomeControl")
Since Controls is the default collection, you can shorten this to:
Me.Controls("SomeControl")

So, this kind of thing:
Dim strField As String
For i = 1 to 4
strField = "CS" & (1000+i)
Me(strField).ControlSource = ...
Next
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a lot of fields on a form/Subform
I wish to change the properties of some of the items via code such as
this:

ME![CS1101].ControlSource = "=([11001] - [11004])"
ME![CS1102].ControlSource = "=([11001] - [11005])"

I have 4 fields similar where [CS1101] = Qtr 1 ...[CS1104] = Qtr 4

Question:
Is there anyway to refer to ME![CS1101].ControlSource indirectly with a
counter?
Where QTR = 1 to 4
ie: ME![CS110" & QTR & "].ControlSource = "=([11001] - [11005])"

Intent is to code 1 set of variables and let a do loop set the others...

 

[Allen Browne]

Sure. Use:
Forms![YourFormNameHere]
instead of Me

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This looks good! Thanks
On a related issue, is this possible to do without using the Me variable
but
in long form?

To use a string for the control name, use:
Me.Controls("SomeControl")
Since Controls is the default collection, you can shorten this to:
Me.Controls("SomeControl")

So, this kind of thing:
Dim strField As String
For i = 1 to 4
strField = "CS" & (1000+i)
Me(strField).ControlSource = ...
Next

I have a lot of fields on a form/Subform
I wish to change the properties of some of the items via code such as
this:

ME![CS1101].ControlSource = "=([11001] - [11004])"
ME![CS1102].ControlSource = "=([11001] - [11005])"

I have 4 fields similar where [CS1101] = Qtr 1 ...[CS1104] = Qtr 4

Question:
Is there anyway to refer to ME![CS1101].ControlSource indirectly with a
counter?
Where QTR = 1 to 4
ie: ME![CS110" & QTR & "].ControlSource = "=([11001] - [11005])"

Intent is to code 1 set of variables and let a do loop set the
others...