validation rule - IsNotNull

[MES via AccessMonster.com]

I am trying to create a validation rule for a data-entry form field (text
field). I want this field to be required; therefore, my validation rule
should not allow null values or zero-strings. Further, I would like to
prevent the user from tabbing and continuing on to the next field until this
field is populated. How would I do this? I tried using "IsNotNull" function,
but that does not seem to work.

Any help would be greatly appreciated. Thanks in advance.

 

[Steve]

I use:

If txtNAME.value = "" or isnull(txtname.value) = true then
intMsg = msgbox("NAME value is empty", vbokonly)
txtname.setfocus
exit sub
end if

 

[Guest]

You can use the before update event of the field or the form depending on how
you want the form to behave. In either case, the code would be:

If Len(Trim(Nz(Me.MyRequiredField,""))) = 0 Then
MsgBox "This field is required"
Cancel = True
'Next line of code only appies if you use the form's before update
Me.MyRequiredField.SetFocus
End If

 

[Dirk Goldgar]

I am trying to create a validation rule for a data-entry form field
(text field). I want this field to be required; therefore, my
validation rule should not allow null values or zero-strings.
Further, I would like to prevent the user from tabbing and continuing
on to the next field until this field is populated. How would I do
this? I tried using "IsNotNull" function, but that does not seem to
work.

Any help would be greatly appreciated. Thanks in advance.

There is no built-in "IsNotNull" function, only an "IsNull" function.
However, in a validation rule (in table design), you can specify a rule
"Is Not Null" (without the quotes). If you also set the field's Allow
Zero Length property to No, then you'll have prevented the user from
saving a record with this field empty. An alternative to the Validation
Rule approach would be to set the field's Required property to Yes (in
addition to forbidding zero-length strings); however, that won't let
you specify a specific message to display, the way the Validation Text
property does.

Be aware, though, that setting these properties doesn't keep the user
from tabbing through the field without entering anything. Unless the
user actually alters the value of the field, the rule is not applied
until the user attempts to save the record. You can use the control's
Exit event to check for an empty field and keep the focus in the
control, but you still have to ensure that the focus goes to that
control in the first place. That's why I prefer to do all my validation
when it comes time to save the record, in the form's BeforeUpdate event.
I don't care what the user puts in the various fields, or what path he
takes through them, until the record is about to be saved. Only then is
it necessary to make sure that the record is valid.

 

[MES via AccessMonster.com]

Thanks for the responses. I have one further question though. I'm using the
following code:

If Len(Trim(Nz(Me.UserMnemonic, ""))) = 0 Then
MsgBox "This field is required."
Cancel = True
End If

It suits my need; however, there is one small issue. I would like the user
to either fill in the required field, or close the form. I have a cancel
button on the form that closes the form, but once the user gets to a required
field, if they do not fill it in with something, they get hung up on that
field. Is there any way to use my cancel button without having to first fill
in the field?

You can use the before update event of the field or the form depending on how
you want the form to behave. In either case, the code would be:

If Len(Trim(Nz(Me.MyRequiredField,""))) = 0 Then
MsgBox "This field is required"
Cancel = True
'Next line of code only appies if you use the form's before update
Me.MyRequiredField.SetFocus
End If

I am trying to create a validation rule for a data-entry form field (text
field). I want this field to be required; therefore, my validation rule

[quoted text clipped - 4 lines]

Any help would be greatly appreciated. Thanks in advance.