Counting Controls

[ddrew]

How can I count the amount of controls on a subform (CR2andWR) and determine how many of them have a value in them i.e Not IsNull. I have a 10 textboxes (A1, A2, A3 up to A10). I want to show the number in a textbox on the mainform (frmAssetManager.Used). I'm thinking that showing it as a percentage is what I would like to do. Many thanks

 

[pr2-eugin]

The following code will check howm many TEXT FIELDS are empty and how many are not..

Dim ctl As Control
Dim someText, onlyNull as Integer
someText = 0
onlyNull = 0
For Each ctl In Me.Controls
    If ctl.ControlType = 109 Then
        If Len(ctl.Value & "")=0 Then
            onlyNull = onlyNull + 1
        Else
            someText =someText + 1
        End If
    End If
Next ctl
MsgBox("Number of NULL Fields " & onlyNull)
MsgBox("Number of Fields with values " & someText)

 

[vbaInet]

By the way, are these textboxes bound?

 

[ddrew]

By the way, are these textboxes bound?

The textboxes are bound, but the one that holds the number of textboxes with a value is unbound.

 

[ddrew]

Is it possible to take this a stage furthur anddo something like:

Dim ctl As Control
Dim someText, onlyNull as Integer
someText = 0
onlyNull = 0
For Each ctl In Me.Controls
    If ctl.ControlType = 109 [COLOR="Red"]WHERE the name of the control name begins with CR2 [/COLOR]Then
        If Len(ctl.Value & "")=0 Then
            onlyNull = onlyNull + 1
        Else
            someText =someText + 1
        End If
    End If
Next ctl
MsgBox("Number of NULL Fields " & onlyNull)
MsgBox("Number of Fields with values " & someText)

 

[John Big Booty]

Try;

Dim ctl As Control
Dim someText, onlyNull as Integer
someText = 0
onlyNull = 0
For Each ctl In Me.Controls
    If ctl.ControlType = 109 [COLOR="Red"]And Left(ctl.Name, 3) = "CR2"[/COLOR] Then
        If Len(ctl.Value & "")=0 Then
            onlyNull = onlyNull + 1
        Else
            someText =someText + 1
        End If
    End If
Next ctl
MsgBox("Number of NULL Fields " & onlyNull)
MsgBox("Number of Fields with values " & someText)

 

[ddrew]

This is superb Final question and I will be able to apply this in so many ways/places is it possible to show the figure as a percentage? I have 10 text boxes begining CR2. Again many thanks for your help.

 

[vbaInet]

The textboxes are bound, but the one that holds the number of textboxes with a value is unbound.

All very good code and I'm sure you can get a percentage from it too, but why don't you do it all in a query?

 

[ddrew]

All very good code and I'm sure you can get a percentage from it too, but why don't you do it all in a query?

I did consider that but wasnt sure where to start with it!:banghead:

 

[vbaInet]

Think:

* Query to Count Nulls
* Subform to display the Counts

 

[ddrew]

Thanks for the advise, but Iv'e got it working now so just need to sort the percetage out (struggling) I have a way but its not based on a calculation, its me working it out manually and hard coding it in (not good) so if anyone can elp me that would be cool, thanks

 

[ddrew]

Iv'e written the code into a module to make it accessible from the main form, but it dosent sem to be working! Any ideas?

Public Function CR2Count()
    Dim someText, onlyNull As Integer
    someText = 0
    onlyNull = 0
    For Each ctl In Forms![frmCR2WR].Controls
        If ctl.ControlType = 109 And Left(ctl.Name, 3) = "CR2" Then
            If Len(ctl.Value & "") = 0 Then
                onlyNull = onlyNull + 1
            Else
                someText = someText + 1
            End If
        End If
    Next ctl
    Forms![frmCR2WR].CountCR2s = (someText)
    Forms![frmCR2WR].AvailCR2s = (onlyNull)
End Function