Hi Sharon,
Please explain the overall picture. On a form that requires user input to
three out of eight fields, where and what type of error code should I use?
for the Save button, for tabbing to the next record, for exiting out of the
form, for a user who attempts to click to the next row before completing the
current row?
new Record: field 1 (R) Field 2 (R) Field 3 (R) Field 4 Field 5
Field 6 (Tab)
New empty row
I think this thread got off on the wrong foot (if threads have feet)
because you asked about VBA error-handling code while it seems that what
you actually want to do is data validation, which is a different matter
entirely. We also need to distinguish carefully between fields (which
are a feature of queries and tables) and controls such as textboxes,
which are used to display data on forms.
Ordinarily a textbox on a form is bound to a text field in the query or
table to which the form is bound.
By default, the control gets the same name as the field it's bound to,
which helps confuse matters. So from now on I'm going to say txtField1
to mean the textbox that is bound to Field1, and so on.
To prevent the user from exiting txtField1 without entering a value, put
code in the textbox's BeforeUpdate event procedure. If it's just to make
sure that *some* value has been entered, something like this will do it
(and similarly for the other textboxes bound to the "required" fields):
Private Sub txtField1_BeforeUpdate(Cancel As Integer)
If IsNull(Me.ActiveControl.Value) Then
Cancel = True
MsgBox "You must enter a value in this field", _
vbOKOnly + vbInformation, "Sharon's Form"
End If
End Sub
To cover the situation where the user moves to the next record without
touching the "required" fields at all, use the Form's BeforeUpdate
event, something like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Controls("txtField1").Value) _
And IsNull(Me.Controls("txtField2").Value) _
And IsNull(Me.Controls("txtField3").Value) Then
Cancel = True
MsgBox "You must enter values for Field1, Field2 and Field3", _
vbOKOnly + vbInformation, "Sharon's Form"
End If
End Sub
If you need to check for incorrect values as well as missing ones, the
principle is the same: use an If or Select Case statement in the
BeforeUpdate event to test the value, and Cancel it and give feedback
with a messagebox if it fails the test.
As the name suggests, BeforeUpdate kicks in before the field or record
is updated, so the user will get your message and not the default one
triggered by an empty "required" field.
