Blank control are highlighted... how can I exclude optional ones?

[Tom]

I have a form with SUBMIT button. When clicked the form checks whether or
not any controls are left blank. If yes, all blank controls are highlighted
in yellow and nothing happens (as intended).

Now, however, I need to exclude some "required" controls from the ForLoop.
Completing them or leaving them empty should be optional.

I attempted to set the tag property to "R" (Required) and "NR" (Not
Required).

Including the (pseudo) "IF = R" into the ForLoop didn't seem to work. Does
anyone know of the right syntax for know of another approach that will
highlight only certain fields?

Thanks,
Tom


===================
Private Sub btnSubmit_Click()

'Declare variables
Dim ctl As Control
Dim NotCompleted As String

'Validation procedure to determine unselected combos and blank textboxes
NotCompleted = False

For Each ctl In Me.Controls

If IsNull(ctl) Then
NotCompleted = True
ctl.SetFocus
ctl.BackColor = vbYellow
End If

Next ctl

If NotCompleted = True Then

MsgBox "You must complete all fields!", vbInformation, "Missing
Data"

Else

'other stuff... such as INSERT statement

End If

End Sub
===================

 

[Guest]

Please post the code that sets the back color. It would be difficult to give
you a solution without know what it is doing currently.

 

[Dirk Goldgar]

I have a form with SUBMIT button. When clicked the form checks
whether or not any controls are left blank. If yes, all blank
controls are highlighted in yellow and nothing happens (as intended).

Now, however, I need to exclude some "required" controls from the
ForLoop. Completing them or leaving them empty should be optional.

I attempted to set the tag property to "R" (Required) and "NR" (Not
Required).

Including the (pseudo) "IF = R" into the ForLoop didn't seem to work.
Does anyone know of the right syntax for know of another approach
that will highlight only certain fields?

Thanks,
Tom


===================
Private Sub btnSubmit_Click()

'Declare variables
Dim ctl As Control
Dim NotCompleted As String

'Validation procedure to determine unselected combos and blank
textboxes NotCompleted = False

For Each ctl In Me.Controls

If IsNull(ctl) Then
NotCompleted = True
ctl.SetFocus
ctl.BackColor = vbYellow
End If

Next ctl

If NotCompleted = True Then

MsgBox "You must complete all fields!", vbInformation,
"Missing Data"

Else

'other stuff... such as INSERT statement

End If

End Sub
===================

You don't show the version you tried that didn't work. It probably
wasn't far wrong (though I wonder why "NotCompleted" is defined as a
String variable, and not Boolean). But I'd probably do something like
this:

'----- start of code -----
Private Sub btnSubmit_Click()

'Declare variables
Dim ctl As Control
Dim strFirstBadControl As String
Dim NotCompleted As Boolean

'Validation procedure to determine unselected combos and blank
textboxes
NotCompleted = False

For Each ctl In Me.Controls

If ctl.Tag = "R" Then

If IsNull(ctl) Then
NotCompleted = True
If Len(strFirstBadControl) = 0 Then
strFirstBadControl = ctl.Name
End If
ctl.BackColor = vbYellow
Else
ctl.BackColor = vbWhite ' or whatever is
"unhighlighted"
End If

End If

Next ctl

If NotCompleted = True Then

MsgBox _
"You must complete all fields!", _
vbInformation, _
"Missing Data"

' Set the focus to the first bad control we found.
Me.Controls(strFirstBadControl).SetFocus

Else

'other stuff... such as INSERT statement

End If

End Sub
'----- end of code -----

 

[Tom]

Dirk:

that works perfect!!!!