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

 

[Uncle Gizmo]

You have already received an excellent answer from moke123

I would add the following observations:-

I think your question needs some clarification, my guess is you mean a macro and not VBA code.

It also appears you are checking for partially filled text boxes, this presents the problem of what conditions represent a completed, or uncompleted text box you need to explain the conditions.
.
As far as I am aware the only way to do this is by looping through the controls with VBA code. Having said that I'm sure somebody has written code which works without a loop, and if they have I'd love to see it. I'm sure it would be an excellent example of why not to do it that way!

Sent from Newbury UK

 

[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,...."

 

[Uncle Gizmo]

Please find attached CountMT_TextBoxes_NiftyAccess.accdb which uses the controls collection as originally suggested by moke123. I strongly suggest you study this method as understanding it will help you take your coding to the next level.

To aid you in understanding the "For Each Ctrl in Me.Controls" method, I have done a YouTube video explaining how the code works...

YouTube:-
Counting Empty Textboxes - Nifty Access

The Function:-

Private Function fSomeMT() As Boolean
'fSomeMT = Some Empty

Dim conAppName As String
conAppName = "(Replace this Local Variable with a Global One) "

Dim strSubName As String
Dim strModuleName As String

strSubName = "fSomeMT"
strModuleName = "Form - " & Me.Name

On Error GoTo Error_Handler

Dim Ctrl As Control
Dim intEmptyCtrls As Integer

    For Each Ctrl In Me.Controls
        Select Case Ctrl.ControlType
       
            Case acTextBox
                If Left(Ctrl.Name, 6) = "tbGame" Then
                    Ctrl.BorderColor = vbBlack
                    Ctrl.BackColor = vbWhite
                   
                        If IsNull(Ctrl) Then
                            intEmptyCtrls = intEmptyCtrls + 1
                            Ctrl.BorderColor = vbRed
                            Ctrl.BackColor = vbYellow
                        End If
                End If
        End Select
       
    Next Ctrl

If intEmptyCtrls > 0 Then
    fSomeMT = True
End If


Exit_ErrorHandler:
   
    Exit Function

Error_Handler:  'Version - 1a
    Dim strErrFrom As String
    Dim strErrInfo As String
       
        strErrFrom = "Error From:-" & vbCrLf & strModuleName & vbCrLf & "Subroutine >>>>>>> " & strSubName
        strErrInfo = "" & vbCrLf & "Error Number >>>>> " & Err.Number & vbCrLf & "Error Descscription:-" & vbCrLf & Err.Description
           
            Select Case Err.Number
                Case 0.123 'When Required, Replace Place Holder (0.123) with an Error Number
                    MsgBox "Error produced by Place Holder please check your code!" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
                Case Else
                    MsgBox "Case Else Error" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
            End Select
        Resume Exit_ErrorHandler

End Function      'fSomeMT

Download File HERE:-

Counting Empty Text Boxes - Nifty Access

A recent question on Access World Forums provided me the opportunity to demonstrate a new variation on one of my favourite coding methods with MS Access VBA:-“For Each Control in Me.Controls”.This technique allows you to look at each control on your Access Form in turn, make a decision about the...

gum.co

A couple of points about your original code:-
I believe "Error" is a reserved word, in other words you could have unintended consequences by using that word in your code... Use a naming convention when you write your code & you avoid this type of Error... I would replace it with a variable that doesn't mean anything in the literal sense... For example use:- "Dim blnError As Boolean"


In your function:-
There's a problem...

'Only this Message is Activated
MsgBox "Please complete all values before continuing.", vbCritical, "Missing data"

'This Message is never Activated
MsgBox "No data has been entered, please complete all values before continuing.", vbCritical, "Missing data"

 

[Uncle Gizmo]

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,...."

When I first started out with MS Access I would use "Let" ... I think I inherited the idea from BASIC. I posted some code on a forum, pretty sure it was Woody's Access Lounge. Someone told me that I shouldn't use "Let" because Microsoft may discontinue it.

It wasn't until years later I discovered that the "Let" Statement is used as you describe, in Property Statements. I realised that it would never be discontinued, and I should have stuck to my guns and kept using it!

These days I do occasionally put it in, mostly to make the code line up and look prettier. There is one really good reason for using it, it distinguishes your code, as in a signature. It would make your code much easier to track down if you thought someone had stolen your code! Now I've revealed that use, I've just taken away the advantage!

 

[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

 

[Uncle Gizmo]

I've added this video, along with an explanation of the For Each Control in Me.Controls method to my website here:- https://www.niftyaccess.com/loop-through-a-set-of-controls/

Where there are some other examples and different uses of the same method.

Sent from Newbury UK