Count number of fields on a form that don;t have a "0" in them

This looks like a repeating group and so should be implemented as 10 rows in a many-side table and in that case you would use a query -- Select Count(*) As NonZeroCount From YourTable Where SomeField <> 0;

To "count" the instances of non zero values, place an expression similar to the following in the ControlSource of a textbox.

=IIf(Text1 = 0, 0, 1) + IIf(Text2 = 0, 0, 1) + IIf(Text3 = 0, 0,1), ....

Or if the controls might be null

=IIf(Nz(Text1,0) = 0, 0, 1) + IIf(Nz(Text2,0) = 0, 0, 1) + IIf(Nz(Text3,0) = 0, 0,1), ....

Here is my code but I'm getting an error when I compile... "Member already exists in an object module from which this object module derives".  Then the line "Function CountNONZero() As Integer" is highlighted in blue.

Function CountNONZero() As Integer

Dim i As Integer, vCount As Integer
    For i = 1 To 10
        If Me("Text" & i) = 0 Then
        vCount = vCount + 1
    End If
    Next
    CountNONZero = vCount

 End Function

Private Sub Form_Current()

    Me.txtCount = CountNONZero()

End Sub

Select allOpen in new window

I like Rey's technique, (and strongly recommend Pat's recommendation of using a table with 10 records and a subform for entering the data) but another technique would be to put the following in the ControlSource of the countNonZero textbox:

ControlSource: = iif(NZ(me.text1, 1) = 0, 0 , 1) + iif(NZ(me.text2, 1) = 0, 0 , 1) +
  iif(NZ(me.text3, 1) = 0, 0 , 1) + iif(NZ(me.text4, 1) = 0, 0 , 1) +  iif(NZ(me.text5, 1) = 0, 0 , 1) + iif(NZ(me.text6, 1) = 0, 0 , 1) +   iif(NZ(me.text7, 1) = 0, 0 , 1) + iif(NZ(me.text8, 1) = 0, 0 , 1) +  iif(NZ(me.text9, 1) = 0, 0 , 1) + iif(NZ(me.text10, 1) = 0, 0 , 1)

Theoretically, this will automatically update itself whenever you make a change in any of those other controls, but you might have to issue a requery in the AfterUpdate event of each of the controls (Text1-Text10).

@SteveL13
were you able to correct the error?

Yes.   I had named the field me.txtCount with a typo.  Once I fixed it all was fine.  Thanks.