ACC2000: You Receive No Warning When Closing a Form with a Required Field Left Null (207657)

SYMPTOMS

When you use code (for example, on a command button) to close a form that contains a control bound to a field that has its Required property set to Yes, and you type no data for that control, you do not receive an error message. Any changes that you made to the record are lost without warning.

If you close the same form by using the Windows Close button, the Close action in a macro, or by clicking Close on the File menu, you do receive one of the following messages as expected:

The field 'tablename.fieldname' can't contain a Null value because the Required property for this field is set to True. Enter a value in this field.

-or-

Field 'tablename.fieldname' can't contain a Null value.

RESOLUTION

Because you have not fulfilled the table's requirements by typing data in the required field, Microsoft Access is not able to add the record to the table. Any other changes that you made to the record are lost. You can work around this behavior by using code to check whether data has been entered in the required field. To cause a warning message to be displayed when you attempt to close the form by using code, follow these steps:

Start Microsoft Access and create a new database.
Create the following new table:
Field Name: Field1
Data Type: Text
Required: Yes

Field Name: Field2
Data Type: Text
Required: No
Close and save the table as Table1. Do not add a primary key when prompted.
In the Database window, select Table1, and then on the Insert menu, click AutoForm.
View the form in Design view, and then add a new command button with the following properties:
Name: CloseForm
Caption: Close Form

Set the CloseForm command button's OnClick property to the following event procedure:

If IsNull(Me![Field1]) Then
  If MsgBox("'Field1' must contain a value." & Chr(13) & Chr(10) & _
  "Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
  "Press 'Cancel' to abort the record.", _
  vbOKCancel, "A Required field is Null") = vbCancel Then
       DoCmd.Close
      
  Else
       Me![Field1].SetFocus
  End If
Else
  DoCmd.Close
End If

View the form in Form view.
Type any data in Field2, and then click the Close Form button.

Note that you receive a message stating that Field1 must contain a value.

MORE INFORMATION

Steps to Reproduce Behavior

Start Microsoft Access and create a new database.
Create a new table with the following fields:
Field Name: Field1
Data Type: Text
Required: Yes

Field Name: Field2
Data Type: Text
Required: No
Close and save the table as Table1. Do not add a primary key when prompted.
In the Database window, select Table1, and then on the Insert menu, click AutoForm.
View the form in Design view and add a new command button as follows:
Name: CloseForm
Caption: Close Form
Add the following code to the OnClick event of the CloseForm button.
DoCmd.Close
On the View menu, select Form View.
Type any data in Field2, and then click the Close Form command button. Note that the form closes without any error message and the record is not saved. However, if you close the form by clicking the Windows Close button or by clicking Close on the File menu, you do receive the messages mentioned in the "Symptoms" section.

REFERENCES

For more information about the Close method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type close method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the Required property, click Microsoft Access Help on the Help menu, type required property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.