Setting up fields values

[JosephMwaba]

Hi There,
I have a form that contains 30 text boxes. i have another text box (box a) on the form. i would like "box a" to count the other 30 boxes only when there is data in it.
e.g if only 4 of the 30 boxes contain data, then "box a" should count 4.
How do i sort this out?
:-(

 

[fneily]

Here's some sloppy code. It works though. Place on the OnCurrent event of your form. (All if statements are on one line). My example shows only three controls. Using copy and paste, you can quickly do 30.
TextBox31 is an UNBOUND textbox for the answer.

Private Sub Form_Current()
Dim holdcount As Integer
If Not IsNull(Me![PersonnelID].Value) Then holdcount = holdcount + 1
If Not IsNull(Me![SSN].Value) Then holdcount = holdcount + 1
If Not IsNull(Me![DutySection].Value) Then holdcount = holdcount + 1
Textbox31.Value = holdcount
End Sub

 

[stix4t2]

Joseph,

One school of thought is to spin all the controls on the form and test for value. You might ask how do it know which fields to include in the count. Well select the controls and set the tag property to something you can test.

Test for tag before using the .value property because other controls, like labels, will generate and error if you try to use a value property.

dim ctrl as control
dim cnt as integer
    for each ctrl in me.controls
          if( ctrl.tag = "Test" )then 'test for tag 
                 cnt = cnt + iif( isnull(ctrl.value ),1,0)
          end if
    next ctrl 
    me.cntControls = cnt

Note: this is sample code, and me.cntControls should be replaced with your control name, and the tag "Test" can be whatever text you want to test.

 

[snyperx3]

Or instead of using the tag, you could use TypeOf to see if the control is a textbox.

-Pete

 

[stix4t2]

snyperx3,

You could test for typeof as well, but the code would count the count text box as well, which is not the spec.

 

[fneily]

You could make the answer a caption for a label, that way it won't be a textbox and won't be counted.
Also, and looping was the original way I tried, in your code, you have ctrl.value - I must have an old version of VBA because .value wasn't an option for me.
TypeOf Textbox works fine.