How to sum a series of unbound text boxes on a form

[markey]

I have a question..... hopefully a simple one. How do you Sum a series of unbound text boxes on a form without manually having to enter each textbox name etc.

Example:

I have a series of text boxes:::

txtTBASS1bonus, txtTBASS2bonus, txtTBASS3bonus... through 25
txtRTF1bonus, txtRTF2bonus, txtRTF3bonus... through 25
txtAHT1bonus, txtAHT2bonus, txtAHT3bonus... through 25

The Sum of all these textboxes = txtPayoutSum

I would imagine the code would be something similar to:

me.txtPayoutSum.value = (Me.txtTBASS1.Value:me.txtTBASS25.Value + Me.txtRTF1.Value:me.txtRTF25.Value + Me.txtAHT1.Value:me.txtAHT25.Value)

This doesn't work. Any help is much appreciated. Thanks.

 

[pbaldy]

For starters, presuming those are fields in a table, you have a design problem (search on normalization). In any case, the colon won't work like it does in Excel. You would have to include each individually.

 

[DCrake]

Simple Software Solutions

Hi
Just picked up this query

One option is to create a function that loops through all the controls on your form and examines the name of the control, if the control name contains the phrase 'Bonus' then if there is a value in this control then add the value to a running sum. at the end of the loop post the totla to the desired control. Remember not to have the total field named 'Bonus'.

Dim DblBonus As Double

For Each control on form

If Control.Name like "*Bonus*" And Val(Control.Name) > 0 then

DblBonus = DblBonus + Val(Control.Name)​

End If
​

Next

This is only a crude version of the code and isnot complete, this is to give you a flavour of what you want to do.

Code Master.

 

[markey]

Thanks!!!

Hi
Just picked up this query

One option is to create a function that loops through all the controls on your form and examines the name of the control, if the control name contains the phrase 'Bonus' then if there is a value in this control then add the value to a running sum. at the end of the loop post the totla to the desired control. Remember not to have the total field named 'Bonus'.

Dim DblBonus As Double

For Each control on form

If Control.Name like "*Bonus*" And Val(Control.Name) > 0 then

DblBonus = DblBonus + Val(Control.Name)​

End If
​

Next

This is only a crude version of the code and isnot complete, this is to give you a flavour of what you want to do.

Code Master.

Thanks for the response. This makes sense. I'll give it a shot. Thanks for your input!

 

[ajetrumpet]

Dim DblBonus As Double

For Each control on form

If Control.Name like "*Bonus*" And Val(Control.Name) > 0 then

DblBonus = DblBonus + Val(Control.Name)​

End If
​

A different version of this might be...

Dim c As Control
Dim RunSum As Single (Double??)

RunSum = 0

  For each c in Me.Controls

    If type of c is Textbox Then
      Val(c) = RunSum + c
      
      RunSum = Val(c)

    Else: Val(c) = Val(c)
    End If

  Next