There's got to be an easier way!

[Deleted member 110146]

Hi All,

I am looking for a "cleaner" way to loop through all of the controls on a sub-form a perform a calculation to fill unbound text boxes. What I am currently doing works, but with 45 of the same line of code increasing by 1 each time, I thought there must be a better way. What I am doing now is:

 Form![Saturday Totals]![Sat1]![Pct 1] = Format((Form![Saturday Totals]![Sat1]![Sat 1] - Form![Saturday Totals]!Sat_tot) / (Form![Saturday Totals]![Sat1]![Sat 1] + [Form]![Saturday Totals]![Sat_tot] / 2), "Percent")
        Form![Saturday Totals]![Sat1]![Pct 2] = Format((Form![Saturday Totals]![Sat1]![Sat 2] - Form![Saturday Totals]!Sat_tot) / (Form![Saturday Totals]![Sat1]![Sat 2] + [Form]![Saturday Totals]![Sat_tot] / 2), "Percent")
        Form![Saturday Totals]![Sat1]![Pct 3] = Format((Form![Saturday Totals]![Sat1]![Sat 3] - Form![Saturday Totals]!Sat_tot) / (Form![Saturday Totals]![Sat1]![Sat 3] + [Form]![Saturday Totals]![Sat_tot] / 2), "Percent")
        Form![Saturday Totals]![Sat1]![Pct 4] = Format((Form![Saturday Totals]![Sat1]![Sat 4] - Form![Saturday Totals]!Sat_tot) / (Form![Saturday Totals]![Sat1]![Sat 4] + [Form]![Saturday Totals]![Sat_tot] / 2), "Percent")
        Form![Saturday Totals]![Sat1]![Pct 5] = Format((Form![Saturday Totals]![Sat1]![Sat 5] - Form![Saturday Totals]!Sat_tot) / (Form![Saturday Totals]![Sat1]![Sat 5] + [Form]![Saturday Totals]![Sat_tot] / 2), "Percent")
        Form![Saturday Totals]![Sat1]![Pct 6] = Format((Form![Saturday Totals]![Sat1]![Sat 6] - Form![Saturday Totals]!Sat_tot) / (Form![Saturday Totals]![Sat1]![Sat 6] + [Form]![Saturday Totals]![Sat_tot] / 2), "Percent")
        Form![Saturday Totals]![Sat1]![Pct 7] = Format((Form![Saturday Totals]![Sat1]![Sat 7] - Form![Saturday Totals]!Sat_tot) / (Form![Saturday Totals]![Sat1]![Sat 7] + [Form]![Saturday Totals]![Sat_tot] / 2), "Percent")
        Form![Saturday Totals]![Sat1]![Pct 8] = Format((Form![Saturday Totals]![Sat1]![Sat 8] - Form![Saturday Totals]!Sat_tot) / (Form![Saturday Totals]![Sat1]![Sat 8] + [Form]![Saturday Totals]![Sat_tot] / 2), "Percent")
        Form![Saturday Totals]![Sat1]![Pct 9] = Format((Form![Saturday Totals]![Sat1]![Sat 9] - Form![Saturday Totals]!Sat_tot) / (Form![Saturday Totals]![Sat1]![Sat 9] + [Form]![Saturday Totals]![Sat_tot] / 2), "Percent")

I have tried looping one line of code with ["Sat " + cstr(x)] and ["Pct" + cstr(x)] but get an error that the item cannot be found.


Hopefully someone has a better way to achieve this.

Thanks for looking,
Bent2Bits

 

[CJ_London]

1. if this code is in your existing form, you don't need to refer to the form.
2. don't format your code in vba - use the format property of the control
3. lose your spaces in your field/control names

The above will reduce this
Form![Saturday Totals]![Sat1]![Pct 1] = Format((Form![Saturday Totals]![Sat1]![Sat 1] - Form![Saturday Totals]!Sat_tot) / (Form![Saturday Totals]![Sat1]![Sat 1] + [Form]![Saturday Totals]![Sat_tot] / 2), "Percent")
to
Pct1= (Sat1 - Sattot) / (Sat1 + Sattot] / 2)

finally you can loop through the controls

 dim i as integer
 for I= 1 to 45
     me("Pct" & I)=(me("Sat" & I) - Sattot) / (me("Sat" & I) + Sattot / 2)
 next I

if the code is in a subform and using values in the main form then try

CODE]
dim i as integer
for I= 1 to 45
me("Pct" & I)=(parent("Sat" & I) - parent.Sattot) / (parent("Sat" & I) + parent.Sattot / 2)
next I
[/CODE]

 

[jdraw]

Please give readers some business context for the question.
What is your business issue in business terms --plain English?
Where does this fit in the grand scheme of things?
People here can be very helpful, but you have to give them something to work with.

PS:
Just noticed CJ had responded. I guess the C in CJ is clairvoyant.

Well done.

 

[arnelgp]

another approach would be to create a Public Function in the Subform:

Public Function fnPercent(intNum As Integer) As Variant
    Dim dblPercent As Double
    dblPercent = Nz(Me![Sat_Tot], 0)
    If dblPercent <> 0 Then
        dblPercent = (Me.Controls("Sat " & intNum) - dblPercent) / _
        (Me.Controls("Sat " & intNum) + dblPercent) / 2
    End If
    fnPercent = FormatPercent(dblPercent, 2)
End Function

then set the Control Source of each [Pct X] textbox control to this function:
for Pct 1, the control source: = fnPercent(1)
for Pct 2, the control source: = fnPercent(2)
for Pct 3, the control source: = fnPercent(3)
... and so on.

 

[Deleted member 110146]

Thank you Arn and CJ - both provided what I was looking for which was how to inject a variable ("Pct " & i) into my formula.

CJ as for the formatting, I tried formatting the control itself, but for some reason the negative percentages just wouldn't play ball - I kept getting all the decimal places +E value. Positive percentages worked perfect giving me 2 decimal places. The VBA Format function didn't have that issue.

Thanks again, always great help here!
Bent2Bits

 

[CJ_London]

never had a problem with formatting negative percentages

have you tried?

0.00%

or
0.00%;-0.00%

or

0.00%;[red](0.00%)

or format as percent and set the dp to 2?

 

[Deleted member 110146]

Hi CJ,

I only tried the "Percent" + 2DP. Positives worked a treat, negatives still acting up. Gave the 0.00%;-0.00% a try and that works perfect for both.

Thanks again mate,
Bent