How can I count how many text boxes have text in them?

[DonLo]

Hi,

I have a form which tracks users who are buying tickets to specific events, and calculates the the cost depending on what they buy. So I have 5 text boxes, one of which is required, the other 4 are not (series1, series2, etc). I'd like to have a calculated control (seriesQuant) show how many of the text boxes have been filled in (i.e, 1,2,3,4 or 5). The value of the seriesQuant contol will be used to calculate what they owe.

So how do I get a seriesQuant to count how many other controls have a value in them.

Thanks

Don

 

[Vic88]

This is a rough idea of what to do. You may have to modify it.

Dim ctl As Control, frm As Form, counter As Integer
Set frm = Forms!form_name


For Each ctl In frm.Controls
If ctl.ControlType = acTextBox Then
If ctl.Value > 0 Then
counter = counter + 1

End If



End If

Next ctl



Hope this helps

 

[Vic88]

Use this instead. It works for both number and text. The one above only work for number
Dim ctl As Control, frm As Form, counter As Integer
Set frm = Forms!form3


For Each ctl In frm.Controls
If ctl.ControlType = acTextBox Then
ctl.SetFocus
If Len(ctl.Text) > 0 Then
counter = counter + 1

End If



End If

Next ctl

 

[JerryDennison]

While Vic88's solution will work for you, you aught to consider normalizing your data instead. You should not have repeating groups(series1, series2 ...).

 

[DonLo]

Thanks,

So would Vic88's code go in an afterUpdate event procedure for the seriesQuant control?

 

[DonLo]

It's been a couple of weeks, but I thought I'd close the loop, and show how I resolved this.

First, in the Class Module for the form I put the following code:

Function SeriesCounter() As Integer
SeriesCounter = 0

If Len(Series) > 0 Then
SeriesCounter = SeriesCounter + 1
End If

If Len(Series2) > 0 Then
SeriesCounter = SeriesCounter + 1
End If

If Len(Series3) > 0 Then
SeriesCounter = SeriesCounter + 1
End If

If Len(Series4) > 0 Then
SeriesCounter = SeriesCounter + 1
End If

If Len(Series5) > 0 Then
SeriesCounter = SeriesCounter + 1
End If

End Function

Series, Series2, etc are the names of the text boxes that I need to count, to have a total that I can put in another text box - SeriesQuant. I do that with the following code:

Private Sub SeriesQuant_Enter()
Me.SeriesQuant.Value = SeriesCounter()
End Sub

It seems to work, so far.