Counting non empty textboxes

[solnajeff]

Hi

I have a form with a large number of text boxes that must be completed. I would like to use a formula if possible to check if any are incomplete rather than loop through using VBA.

I feel sure that it should be possible but cannot find any reference on the web.

Any advice would be appreciated.

Regards

Jeff

 

[moke123]

The easiest way is to loop through the form controls, especially if you have a large number of then.
I use the following public function. In the tag property of the controls to check add "V8". It fires a message box of the missing fields and returns a boolean value.

Public Function IsfrmValid(frm As Form) As Boolean

    Dim ctl As Variant
    Dim flg As Boolean
    Dim strMsg As String

    flg = True

    For Each ctl In frm.Controls

        If InStr(1, ctl.Tag, "V8") Then

            If Nz(ctl, "") = "" Then

                flg = False

                ctl.BorderColor = vbRed

                strMsg = strMsg & Space(20) & "* " & ctl.Controls.Item(0).Caption & vbNewLine

            Else

                ctl.BorderColor = vbBlack

            End If

        End If

    Next ctl

    IsfrmValid = flg

    If flg = False Then

        MsgBox "The following item(s) are required:" & vbNewLine & vbNewLine & strMsg

    End If

End Function

 

[theDBguy]

Hi. You can use the principle of null propagation to determine if any textbox is empty without using a loop, but it won't tell you which one is empty, only that there is at least one empty box.

 

[jdraw]

Jeff,
More info needed. Perhaps you could define "incomplete" as it relates to your project.
A few examples or scenarios describing the before and after of your "textbox checking process" would help with context.
As always, clarity in the requirement description will expedite your receiving more focused responses.

 

[solnajeff]

Hi

Sorry I could not post yesterday and thank you to all responders.

I am attaching a sample illustrating what I used that generates an error message if there is no data or if an empty box, or boxes, is left in any of the groups (both five and three).

Once again many thanks

Regards

Jeff

 

[arnelgp]

you already have the sub there, only some slight modification:

Private Sub cmdSetMInMax_Click()

    Dim tbmin As Integer
    Dim tbmax As Integer
    Dim gnum As Integer
    Dim error As Boolean
    Dim count As Integer
    Dim totcount As Integer


    For gnum = 1 To 9

        Let count = 0
        For tbmin = 1 To 5
            If Not IsNull(Me.Controls("tbgamesMin" & gnum & tbmin)) Then
                Let count = count + 1
                Let totcount = totcount + 1
            Else
                Exit For
            End If
        Next tbmin

        If count < 5 Then
            Let error = True
            Exit For
        End If

        Let count = 0
        For tbmax = 1 To 3
            If Not IsNull(Me.Controls("tbgamesMax" & gnum & tbmax)) Then
                Let count = count + 1
                Let totcount = totcount + 1
            Else
                Exit For
            End If
        Next tbmax

        If count < 3 Then
            Let error = True
            Exit For
        End If

    Next gnum

    If error = True Then

        MsgBox "Please complete all values before continuing.", vbCritical, "Missing data"
        End

    End If

    If totcount = 0 Then

        MsgBox "No data has been entered, please complete all values before continuing.", vbCritical, "Missing data"
        End

    End If
    
    
    ' Additional code here

End Sub

 

[jdraw]

Just a comment - I don't recall ever seeing the Let statement outside a Property Procedure. As arnelgp's tag line says - "Never stop learning,...."

 

[isladogs]

Or to put it a different way, I'm fairly sure arnel's code would work exactly the same if all instances of Let were removed
For example just use TotCount=TotCount+1

BTW in addition to Error, Count is a reserved word so I would use e.g. iCount instead.

 

[solnajeff]

Hi

Once again thanks for all the advice and the video.

Regards

Jeff