Reduce code

[George Too]

Hi all,

I was wondering how to reduce the following code:

'***********************
'Initialize variables
N = 0 'Number of rolls
A = 0 'Sum of All entries

'Add fields
If Not IsNull([subfrmRepeats].[Form]![Roll1]) And [subfrmRepeats].[Form]![Roll1] <> 0 Then
A = A + [subfrmRepeats].[Form]![Roll1]
N = N + 1
End If
If Not IsNull([subfrmRepeats].[Form]![Roll2]) And [subfrmRepeats].[Form]![Roll2] <> 0 Then
A = A + [subfrmRepeats].[Form]![Roll2]
N = N + 1
End If
If Not IsNull([subfrmRepeats].[Form]![Roll3]) And [subfrmRepeats].[Form]![Roll3] <> 0 Then
A = A + [subfrmRepeats].[Form]![Roll3]
N = N + 1
End If
'***********************

I have 30 of these [RollN] controls in the subform "subfrmRepeats".

Now, I have seen somewhere that I can use something like this:

For Each ctl In subfrmRepeats.Controls

but I don't know what else follows.

Thank you.

 

[PaddyIrishMan]

   For Each Control In Me.Controls
        If Control.ControlType = acTextBox Or Control.ControlType = acComboBox Then
             'Do Something
         End if
    Next

This code will loop thru all of the controls on the form.
Bear in mind that it will recognise all controls so you may want to include an if statement like the above to ensure that only the relevant control types are affected. There is a list of constants in the VBA Help.

HTH,
Patrick

 

[George Too]

Hi Patrick,

Your code works nicely, but I run into a problem that I don't know how to solve. All the controls in the subform are text boxes so I don't need that line of code you talk about.

What I do need though is a line of code that will exclude a particular text box out of the 31. What would this line of code look like?

Thank you.

George

 

[PaddyIrishMan]

No Probs,
What you need is an if statement:

If Control.ControlName = "TheControlNameYouWantToSkip" then
     'Do nothing
Else
    'Do Something
End if

Replace "TheControlNameThatYouWantToSkip" with (You guessed it) The name of the control that you want to skip.

Regards,
Patrick

 

[George Too]

Hi Patrick,

I was trying something similar except that I missed the ".controlName" part.
Code is working like a charm...

Thanks a lot!