If any fields in a form is empty then (1 Viewer)

[hfl]

Hello,

I have a small problem I can't find the answer to, been looking and trying different things for almost two days now.

I have a form with about 90 text-boxes (logging form for a test) and I am trying to make it so that when all fields are filled out then a label turn green and turn red if any of the fields is empty.

Here is the code I have so far

Private Sub Form_BeforeUpdate(Cancel As Integer)

    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox
                If Len(Nz(ctl.Value, vbNullString)) = 0 Then
                 
                        Me.Parent!btn_FlowCurveLog.BackColor = vbRed
                        
                        Else
                        
                        Me.Parent!btn_FlowCurveLog.BackColor = vbGreen

                End If
        End Select
    Next ctl
    Exit Sub

End Sub

The problem is that as soon as the form see a value in any of the fields then the label turn green.

Any help is appreciated.

 

[Minty]

Set the control to Green before you check for any nulls then turn red on any null then exit the loop, you don't need to keep checking?

 

[hfl]

Thanks for the replay but green indicates that the form is finished so can't have it start out green or is that not what you meant?

I would like it to check every time someone goes in and out of that form yes.

 

[Minty]

Sort of - in fact don't set it to anything, check for nulls if you get one make it red and exit the loop, or change it to green because you didn't find any nulls.

 

[hfl]

But there is my problem, how do I set it to red if it find any fields with nulls? Right now all fields have to be null for it to be red and it turn green as soon as one of the 90 fields have a value.

 

[Minty]

I'm assuming that your code correctly found the nulls? If so you need to run the loop as

LoopStart

check field for null value
If Yes then Red and Exit
Goto Next Check Next Field

Loop End
IF it gets here all fields must have a value
Label = Green
End

 

[JohnHBooth]

I think if you amend your code like this you will get what you want:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim blnAllEntered As Boolean
blnAllEntered = True
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox
                If Len(Nz(ctl.Value, vbNullString)) = 0 Then
                        Me.Parent!btn_FlowCurveLog.BackColor = vbRed
                        blnAllEntered = False
                End If
        End Select
    Next ctl
    
    If blnAllEntered Then
        Me.Parent!btn_FlowCurveLog.BackColor = vbGreen
    End If
    Exit Sub
End Sub

 

[hfl]

Thanks JohnHBooth, that worked perfectly