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.
Hi, Does this work the same way for Submit button click?
ReplyDeleteI'm not sure what you mean by a Submit button. I use unbound forms and create a Save button on the form. The above code is then included in the On_Click event of the Save button. If you can insert this code into the code that fires when you click your Submit button it should work.
ReplyDeleteHi, Thank you for the response. The form I have is for SharePoint linked access database. So Fields in the form are bound to the columns in the access database. Once the user fills in all data using the combo boxes and text fields, they can hit the submit button which will push the form data to the DB. However, if some of the fields which are supposed to be field are left blank by users, there should be a pop up to notify the user of the mandatory fields. Your code above (bless you for sharing it with the rest of the world!!) seems to work well for a unbound form but does not react to bound fields. Since I'm very new (2 weeks old) to MS access i tried my excel vba to it and failed to accomplish. could you perhaps guide me on this?
ReplyDeleteHi Bloggerking, that is one of the reasons I use unbound forms almost exclusively. It does require more coding, but I can better control what happens with the data. I have never used SharePoint, nor have I ever used VBA in Excel, so I can't really help you there. Sorry, I wish I could be more help.
ReplyDelete