Validation code

[timothyl]

Hello, I am tiring to write a (more or less) all purpose validation code, what I have so far works except for one thing, if there are multiple controls needing validation and you execute a command with out entering data the focus will move to the next control failing validation and so forth until the last control, were it will finally keep focus until you enter a value, Any insight in to how to keep focus on the control that first fails validation until value is entered is appreciated



Private Sub cmdClose_Enter()
Dim nl As String, ctl As Control
nl = vbNewLine & vbNewLine

For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If Trim(ctl & "") = "" And ctl.Tag = "v" Then
Msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
"Required Data!" & nl & "Please enter the data and try again ... "
Style = vbInformation + vbOKOnly
Title = "Required Data..."
MsgBox Msg, Style, Title
ctl.SetFocus
Cancel = True
End If
Case Else
End Select
Next ctl
End Sub

 

[HiTechCoach]

A problem with your code that I noticed is that you have:

Cancel = True

and this event does not have the option to cancel it.

I think your code should probable be in the form's Before Update event is you are trying to cancel it.

 

[CEH]

Just have two things to add... You might consider writing it as a function. Then call it from your Before update. This way you could use it on multiple forms if needed.
I have also found it is much easier to alter this standard code just a bit to make more sense to your users.... When they see the error message they might not recognize the "ctl.Name" as they shouldn't.... But they would recognize the label name... "ctl.Controls(0).Caption" Plus it simply looks a little more professional.

 

[timothyl]

HiTechCoach, CEH, thank you for responding. CEH I took your advice and made it a function as well as your "ctl.Controls(0).Caption" vary good idea's.


Function Validate(v As String, frm As Form)

Dim nl As String, ctl As Control
nl = vbNewLine & vbNewLine

For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If Trim(ctl & "") = "" And ctl.Tag = "v" Then
Msg = "Data Required for '" & ctl.Controls(0).Caption & "'" & nl & _
"Required Data!" & nl & "Please enter the data and try again ... "
Style = vbInformation + vbOKOnly
Title = "Required Data..."
MsgBox Msg, Style, Title
ctl.SetFocus
Cancel = True
End If
Case Else
End Select
Next ctl
End Function