On some of the data entry forms that I use there are fields
(text box controls) that are required and others that are optional. Since I
normally use unbound forms I can easily identify blank required fields and
remind the user to fill them in.
On this form has three controls that are for required
fields:
If the user leaves the Collection Number, the Scope/Content
Note, or the Languages blank I need the code to identify the blank required
fields and prompt the user to filled them in. This is pretty easily done by
using the function Nz() to convert the null values from the required fields to “DataError”
and the optional ones to a zero-length string.
In the On_Click event of the Save button (cmdSave_Click), after
dimensioning the variables I pick up the values from the controls and use “DataError”
to isolate where the user needs to add data:
strCollNum = Nz(Me.txtCollNum,
"DataError")
strTitle = Nz(Me.txtTitle, "")
strScopeContent = Nz(Me.txtScopeContent,
"DataError")
strType = Nz(Me.txtType, "")
strFormat = Nz(Me.txtFormat, "")
strExtent = Nz(Me.txtExtent, "")
strDate = Nz(Me.txtDate, "")
strLocation = Nz(Me.txtLocation, "")
strRestrictions = Nz(Me.txtRestrictions,
"")
strRelation = Nz(Me.txtRelation, "")
strLanguage = Nz(Me.txtLanguage, " DataError
")
strSubjects = Nz(Me.txtSubjects, "")
strKeywords = Nz(Me.txtKeywords, "")
strNotes = Nz(Me.txtNotes, "")
'Put the three required fields together
in one string, strError.
strError = strCollNum & strDesc
& strLanguages
'Test the strError to see if it
contains DataError.
If strError Like
"*DataError*" Then
'If strError contains DataError test
each required field.
If strCollNum Like
"DataError" Then
'For a DataError make the back
color for the text box red.
Me.txtCollNum.BackColor = RGB(255, 0, 0)
Else:
'Otherwise the back color for the
text box should be the original blue.
Me.txtCollNum.BackColor =
RGB(199, 208, 227)
End If
If strDesc Like
"DataError" Then
'For a DataError make the back
color for the text box red.
Me.txtDescription.BackColor =
RGB(255, 0, 0)
Else:
'Otherwise the back color for the
text box should be the original blue.
Me.txtDescription.BackColor =
RGB(199, 208, 227)
End If
If strLanguages Like
"DataError" Then
'For a DataError make the back
color for the text box red.
Me.txtLanguage.BackColor = RGB(255, 0, 0)
Else:
'Otherwise the back color for the
text box should be the original blue.
Me.txtLanguage.BackColor =
RGB(199, 208, 227)
End If
'Prompt the user to fill in the
missing date and exit the sub.
MsgBox "Red fields are
required. Please complete form and re-save.", vbCritical
Exit Sub
Else:
'The
code to write the data to the tables would go here to execute when all three
'required fields contain data.
End If
Here is what the form looked like when I tried to save it
after entering a collection number without a Scope/Content Note or Language.
Since the sub was exited after the message box was presented
the user will be returned to the form when the message box is closed: the
record cannot be saved unless the required fields contain data. Whether or not they
contain valid data is another question, but using a similar set of tests the
data can be validated and rejected if not valid.
