required fields

[Lori]

I have a form called frm_Vendor. I have multiple required fields for saving
a record. How can I enforce that all fields must be entered in order to
save a new record? I have the Required Property set to Yes at the table
level also.

I put the following code in the form BeforeUpdate event to give the user an
error message if they try to close the form without entering all required
fields(this one is for the Chain Suffix and Region fields) I have done the
same for each required field. When I try to close the form, I get a bunch of
error messages, that I click OK on and then the form closes. I know I am
missing something, any suggestions?

If IsNull(Me.Chain_Suffix) = True Then
MsgBox "Please select a Chain Suffix"
Cancel = True
End If

If IsNull(Me.Region_Code) = True Then
MsgBox "Please select a Region"
Cancel = True
End If

 

[Allen Browne]

Settting the Required property of each field in your table should be enough
to prevent Access saving the record if the field is null.

If you attempt to close the form while some required fields are left blank,
you should get a message indicating that the record cannot be saved and
asking whether to continue closing (and lose the entry), or complete the
entry before closing. At least, that's what happens if you click the close
button on the right-end of the form's title bar; if you use the Close
action/method in a macro/code, Access fails to give this message. For a
workaround, see:
http://allenbrowne.com/bug-01.html

You can also use the BeforeUpdate event if you wish. Make sure you are using
the BeforeUpdate of the *form*, not of the controls: the control's events
won't work, because they don't fire if nothing is entered.

If you did use Form_BeforeUpdate, but you using DoCmd.Close (or the close
action in a macro), Access does close the form and lose the entry. This is
the same bug discussed in the article link above, and the solution is the
same (i.e. explicitly save before using Close.)

It might be nicer to give the user a single box explaining what is wrong
with the record instead of a whole series. You can do that like this:

Private Sub Form_BeforeUpate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.Chain_Suffix) Then
strMsg = strMsg & "Please select a Chain Suffix" & vbCrLf
Cancel = True
End If

If IsNull(Me.Region_Code) Then
strMsg = strMsg & "Please select a Region" & vbCrLf
Cancel = True
End If

If strMsg <> vbNullString Then
MsgBox strMsg, vbExclamation, "Invalid entry"
End If
End Sub

 

[Lori]

Thanks for your help and additional information! It worked great!

 

[Lori]

Hi Allen,

I used your code and it is working great except I am getting an extra error
message after my required fields missing message. It is an Access error that
displays after I click OK on the missing required fields message. It simply
states "No Current Record". Why am I getting this one? Any ideas? I have
also received a message saying the DoCommand Item was cancelled.

Thanks for any help you can give!

Lori

 

[Allen Browne]

If the field's Required property is set to Yes and you just bypass the
field, you won't get that message. But if you start and entry in the control
and then backspace it out, you will get an engine level error notifying you
that the entry you made (which is now Null) is unacceptable. You can avoid
the error by pressing Esc to undo the field, which takes you back to the
same place as if you had not started making the entry. (If you want to trap
this error programmatically, you can in the Error event of the form.)

An alternative approach is to go back to the table and set the Required
field to No. Instead, set the Validation Rule property for the field (in the
lower pane of table design) to:
Is Not Null
You can then type the message you want to see beside the field's Validation
Text property, e.g.:
You can't leave the Chain Suffix blank. Enter a value or press <Esc> to
undo.

The No Current Record error makes no sense to me, unless you are actually
deleting the record, or runnning some other code.

 

[Lori]

Thanks for the suggestions - I tried both and I am still getting the No
Current Record error after the first message about missing the required
fields. I didn't start any entry in a field and then back out so I'm not
sure why it's happening. I tried entering data in one field and then closing
(purposely not entering all required fields) and the No Current Record pops
up after the required fields error. Thanks for trying - I appreiciate it!