ACC: No Warning Closing a Form with a Required Field Left Null

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

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 no data is entered for that control, you do not receive an error message.

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 the following message 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

By default, the changes to the record will be aborted without warning. If you need to check whether data has been entered in the required field, you will need to do so manually in code, as shown in the following example:

1. Start Microsoft Access and create a new database.

2. Create the following new table:

         Table:   Table1
         ------------------
         Field Name: Field1
            Data Type: Text
             Required:  Yes
         Field Name: Field2
            Data Type: Text
             Required:   No
   
   

3. Close and save the table as Table1. Do not add a primary key when prompted.

4. In the Database window, select the Table1 table, and then on the Insert menu, click AutoForm.

5. View the form in Design view and add a new command button as follows:

         Name: CloseForm
         Caption: Close Form
   

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

   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. If you are using Microsoft Access 2.0, remove the underscore from the end of the line when re-creating this code.

         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
                ' For Access 2.0 remove the previous line and replace with:
                ' DoCmd Close
           Else
                Me![Field1].SetFocus
           End If
         Else
           DoCmd.Close
         End If
   
   

7. View the form in Form View.

8. Type any data in Field2 and click the Close Form button. Note that you receive a message stating that Field1 must contain a value.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start Microsoft Access and create a new database.

2. Create the following new table:

         Table:   Table1
         ------------------
         Field Name: Field1
            Data Type: Text
             Required:  Yes
         Field Name: Field2
            Data Type: Text
             Required:   No
   
   

3. Close and save the Table1 table. Do not add a primary key when prompted.

4. In the Database window, select the Table1 table, and then on the Insert menu, click AutoForm.

5. View the form in Design view and add a new command button as follows:

         Name: CloseForm
         Caption: Close Form
   

6. Add the following code to the Click event of the CloseForm button.

   In Microsoft Access 95 or 97:

         DoCmd.Close
   

   In Microsoft Access 2.0:

         DoCmd Close
   

7. On the View menu, select Form View.

8. Type any data in Field2 and click the Close Form command button.

However, if you close the form by using 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, search the Help Index for "Close Method."

For more information about the Required property, search the Help Index for "Required Property."