B
BruceM
This past summer we had a computer science student (one semester to go, and
a smart guy, so pretty knowledgeable in general) working on various
projects. One was a database I had been developing. He provided valuable
assistance in quite a number of areas, but in the matter of using validation
in the form's Before Update event to verify that required fields contain
values, he may have gone a bit code-happy.
The form is divided into four tab pages, each of which contains controls
pertaining to a different aspect of the information that needs to be
recorded (Problem Description, Response, etc.). My approach was to use a
sub to loop through the controls on the current page. blnNoValidate is a
Boolean that is defined in the form's Declarations section.
Public Sub CheckSection(ValTag As String)
For Each ctl In Me.Controls
If ctl.Tag = ValTag Then
If IsNull(ctl) Then
blnNoValidate = True
Else
blnNoValidate = False
Exit For
End If
End If
Next ctl
End Sub
In the form's Before Update event:
Call CheckSection("Resp")
If blnNoValidate = True Then
Exit Sub
Else ' Assure that all fields on the Response tab are filled in
If IsNull(Me.txtActionTaken) Then
Select Case MsgBox(strAction, vbOKCancel)
Case vbOK
Me.txtActionTaken.SetFocus
Cancel = True
Case Else
Me.Undo
End Select
ElseIf IsNull(Me.txtCause) Then
Select Case MsgBox(strCause, vbOKCancel)
Case vbOK
Me.txtCause.SetFocus
Cancel = True
Case Else
Me.Undo
End Select
End If
End If
If IsNull(Me.cboAssignFollowUp) Then Exit Sub
Call CheckSection("Fol")
etc.
There are actually six controls on the Resp tab. I have shortened the code
to make it more readable.
To summarize, the form's Before Update event calls CheckSection("Resp"). If
any controls on the Response tab page (with the tag "Resp") contain a value,
they must all contain a value. The strings (strAction, strCause) are
messages defined in the Declarations section. If a control is null, the
user is given the opportunity to add a value or to exit without saving.
After checking all of the controls on the Response tab, the code checks
cboAssignFollowUp to see if anybody has been assigned to the follow-up. If
not, no need to check further.
I have a few questions about this approach. First, is it reasonable? Can
it be tightened up so that it runs more efficiently?
Assuming it is generally OK:
In CheckSection, would it make sense to have code to first see if the
control is a text box or combo box?
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If ctl.Tag = ValTag Then
etc.
(I would have to Dim ctl as Control, of course)
There are more labels and other controls that do not need to be checked, so
would this shorten the loop time?
What the computer student came up with is beyond my ability to fully
comprehend (at least any time soon), let alone to explain concisely. It
involved using the form's Load event to set up arrays for error messages and
for the controls to validate. These arrays are used to generate an error
message if a control's value is invalid (when another function in the Before
Update event loops through the controls). I could go into greater detail if
need be, but what I really want to know is whether I am wasting significant
processing time with my original system. There are about fifteen or twenty
controls to validate on the entire form (all four tab pages). If my
original idea is OK, it has the value of being something I can understand
now.
a smart guy, so pretty knowledgeable in general) working on various
projects. One was a database I had been developing. He provided valuable
assistance in quite a number of areas, but in the matter of using validation
in the form's Before Update event to verify that required fields contain
values, he may have gone a bit code-happy.
The form is divided into four tab pages, each of which contains controls
pertaining to a different aspect of the information that needs to be
recorded (Problem Description, Response, etc.). My approach was to use a
sub to loop through the controls on the current page. blnNoValidate is a
Boolean that is defined in the form's Declarations section.
Public Sub CheckSection(ValTag As String)
For Each ctl In Me.Controls
If ctl.Tag = ValTag Then
If IsNull(ctl) Then
blnNoValidate = True
Else
blnNoValidate = False
Exit For
End If
End If
Next ctl
End Sub
In the form's Before Update event:
Call CheckSection("Resp")
If blnNoValidate = True Then
Exit Sub
Else ' Assure that all fields on the Response tab are filled in
If IsNull(Me.txtActionTaken) Then
Select Case MsgBox(strAction, vbOKCancel)
Case vbOK
Me.txtActionTaken.SetFocus
Cancel = True
Case Else
Me.Undo
End Select
ElseIf IsNull(Me.txtCause) Then
Select Case MsgBox(strCause, vbOKCancel)
Case vbOK
Me.txtCause.SetFocus
Cancel = True
Case Else
Me.Undo
End Select
End If
End If
If IsNull(Me.cboAssignFollowUp) Then Exit Sub
Call CheckSection("Fol")
etc.
There are actually six controls on the Resp tab. I have shortened the code
to make it more readable.
To summarize, the form's Before Update event calls CheckSection("Resp"). If
any controls on the Response tab page (with the tag "Resp") contain a value,
they must all contain a value. The strings (strAction, strCause) are
messages defined in the Declarations section. If a control is null, the
user is given the opportunity to add a value or to exit without saving.
After checking all of the controls on the Response tab, the code checks
cboAssignFollowUp to see if anybody has been assigned to the follow-up. If
not, no need to check further.
I have a few questions about this approach. First, is it reasonable? Can
it be tightened up so that it runs more efficiently?
Assuming it is generally OK:
In CheckSection, would it make sense to have code to first see if the
control is a text box or combo box?
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If ctl.Tag = ValTag Then
etc.
(I would have to Dim ctl as Control, of course)
There are more labels and other controls that do not need to be checked, so
would this shorten the loop time?
What the computer student came up with is beyond my ability to fully
comprehend (at least any time soon), let alone to explain concisely. It
involved using the form's Load event to set up arrays for error messages and
for the controls to validate. These arrays are used to generate an error
message if a control's value is invalid (when another function in the Before
Update event loops through the controls). I could go into greater detail if
need be, but what I really want to know is whether I am wasting significant
processing time with my original system. There are about fifteen or twenty
controls to validate on the entire form (all four tab pages). If my
original idea is OK, it has the value of being something I can understand
now.