Shortening Code 2

[Jengo]

Is there any way I can shorten this code into a for statement?


If Not IsNull(DOLLAR_1) Then tmp = tmp + DOLLAR_1
If Not IsNull(DOLLAR_2) Then tmp = tmp + DOLLAR_2
If Not IsNull(DOLLAR_3) Then tmp = tmp + DOLLAR_3
If Not IsNull(DOLLAR_4) Then tmp = tmp + DOLLAR_4
If Not IsNull(DOLLAR_5) Then tmp = tmp + DOLLAR_5
If Not IsNull(DOLLAR_6) Then tmp = tmp + DOLLAR_6
If Not IsNull(DOLLAR_7) Then tmp = tmp + DOLLAR_7
If Not IsNull(DOLLAR_8) Then tmp = tmp + DOLLAR_8
If Not IsNull(DOLLAR_9) Then tmp = tmp + DOLLAR_9
If Not IsNull(DOLLAR_10) Then tmp = tmp + DOLLAR_10

dollar_* are textboxes
tmp is a variable that stores all of the dollar_* that have a value.

 

[Psychpt]

for x= 1 to however many textboxes
If Not IsNull("DOLLAR_" &x) Then tmp = tmp + "DOLLAR_"& x
next x

Hope this helps

 

[Jengo]

What is wrong with this code?

for x=1 to 10
If Not IsNull("DOLLAR_" & x) Then tmp = tmp + "DOLLAR_"& x
next x

I am receiving a type mismatch error.

Dollar_* are textboxes that I am retrieving values from.

 

[JerryDennison]

Paste the following funtion into a module. You can then use it with any form. To display the total of any number of controls that begin with a common name pass the form and the first few characters of the common name to the function. For example, in an unbound control on your form set it's control source to the following:

=SumCommonControls(Forms!FormName, "DOLL&quot

Public Function SumCommonControls(frm As Form, txtName As String) As Double
Dim tmp As Double
Dim ctl As Control
For Each ctl In frm.Controls
If ctl.ControlType = acTextBox Then
If Left(ctl.Name, Len(txtName)) = txtName Then
tmp = tmp + Nz(ctl.Value)
End If
End If
Next
SumCommonControls = tmp
End Function

 

[PaulF]

Jengo
"Dollar_ "& x should be "Dollar_" & cstr(x)
you need to convert the integer x to a string

PaulF

 

[Jengo]

The last suggestion didn't work either.

 

[rafe]

my two cents...

i just did a mock-up to verify this

you also need to reference the control using the parentheses notation

so change Jengo's 1st solution to...

for x=1 to 10
tmp = tmp + Nz(Me("DOLLAR_"& x),0)
next

fyi... here's my test code & is worked just fine

Private Sub Command6_Click()
Dim i As Integer
Dim sum As Double

sum = 0
For i = 0 To 2
sum = sum + Me("Text" & i).Value
Next
Me.Text3 = sum
End Sub

 

[rafe]

credit should have gone to psychpt

jerry's solution works well... i'm just in the habit of doing things differently... it'll give you a choice of solutions

rafe

 

[Jengo]

Thank you all for your help!