A simple way to do this is to set the Required property of the field in the
table to No.

Instead, set the Validation Rule to:
   Is Not Null
and you can then set the Validation Text to whatever you want.

To trap the error at the form level instead, use the Error event of the
form. Set up your own error message, and set the Response argument to
acDataErrContinue.

"=?Utf-8?B?SmltIE5vcm1pbGU=?=" <JimNormile@discussions.microsoft.com>
wrote in news:A1ABDF17-C4EB-4F15-8E01-FDB07BD2DE99@microsoft.com:

You can use the BeforeUpdate event of the form, along the lines of

 private sub Form_BeforeUpdate( cancel as integer)

   if isnull(txtSurname) then
     ' warn the user
     msgbox "you have to fill in the surname, stoopid!"
     ' send the user back to the correct control
     txtSurname.SetFocus
     ' and prevent the form going ahead with the update
     Cancel = True

   elseif isnull(txtForeName) then
     ' warn the user
     msgbox "you have to fill in the forname, too!"
     ' etc etc

Depending on your circumstances, you may think of ways to streamline
this. Don't use the control's BeforeUpdate because it won't fire if the
user has not changed it.

Another approach is to avoid the situation altogether. If the field
really is mandatory, you might want to put a DefaultValue in the table
description. If you then end up with a lot of clients called
"DefaultForename DefaultLastName" then you know you have a user training
issue to deal with. Alternatively, you could demand a Name before letting
the user attempt to create a new record -- use the Form_Load event for
this. If there are genuine times when a person's name is unknown, then
you may be better off removing the Required attribute altogether. I am
generally suspicious of required fields that don't have obvious default
values too.

Hope that helps

Tim F