ACC: Edits Not Processed w/ Close Action on Form (2.0, 7.0, 97)

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

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

When you edit a record in a form, the editing changes are not processed when the form is closed with a macro's Close action. However, you are not prompted with any error messages as to why the changes are not saved.

CAUSE

The Close action is invoked with the DoCmd method in Visual Basic for Applications (or the DoCmd statement in Access Basic in Microsoft Access 2.0) and the current record is not processed because a validation error has occurred. The following validation errors can prevent editing changes from being processed when the form closes:

• A validation rule violation.
• A primary key or index violation.
• A referential integrity violation.

RESOLUTION

To work around this problem, force the record to be saved before the Close action is run by using the following RunCommand method (or DoMenuItem method in Microsoft Access 2.0 and 7.0) to select Save Record on the Records menu (or File menu in Microsoft Access 2.0).

In Microsoft Access 97

   DoCmd.RunCommand acCmdSaveRecord

   NOTE: In Microsoft Access 97, the DoMenuItem method has been replaced by
   the RunCommand method. The DoMenuItem method is included in Microsoft
   Access 97 only for compatibility with earlier versions.

In Microsoft Access 7.0

   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord,, acMenuVer70

In Microsoft Access 2.0

   DoCmd DoMenuItem A_FormBar, A_File, A_SaveRecord, , A_Menu_Ver20

In Microsoft Access 7.0 and 97

   Private Sub CloseForm_Click()
   On Error GoTo Err_CloseForm_Click

     DoCmd.Close

   Exit_CloseForm_Click:
   Exit Sub

   Err_CloseForm_Click:
     MsgBox Err.Description
     Resume Exit_CloseForm_Click

   End Sub

In Microsoft Access 2.0

   Sub CloseForm_Click ()
   On Error GoTo Err_CloseForm_Click

      DoCmd Close

   Exit_CloseForm_Click:
      Exit Sub

   Err_CloseForm_Click:
      MsgBox Error$
      Resume Exit_CloseForm_Click

   End Sub

Also, use the On Error GoTo statement to trap any error that may occur.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access versions 2.0, 7.0, and 97. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Problem

In Microsoft Access 7.0 and 97

1. Open the sample database Northwind.mdb, and open the Customers form in Design view.

2. Make sure that the Control Wizards button is selected in the Toolbox before creating the command button.

3. Add a command button to the form.

4. In the Command Button Wizard dialog box, click Form Operations in the Categories box, click Close Form in the Actions box, and then click Finish.

5. Save the Customers form, and then view the form in Form view.

6. On the Edit menu, click Go To, and then click New Record on the Go To submenu.

7. Type "ABCDE" (without the quotation marks) in the CustomerID field, and then close the form. Note that the following error message appears:

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

8. If you then click OK, you will receive the following additional message:

         Microsoft Access
         You can't save this record at this time. Microsoft Access may have
         encountered an error while trying to save a record. If you close
         this object now, the data changes may be lost.
   

         Do you want to close the Database object anyway?
   

9. Click No, and then click the Close button that you created. Note that the form closes without an error message. However, the new record with CustomerID "ABCDE" is not processed.

In Microsoft Access 2.0

1. Open the sample database NWIND.MDB, and open the Customers form in Design view.

2. Make sure that the Control Wizards is selected (has a check mark) on the View menu.

3. Add a command button to the form. Note that the Command Button Wizard dialog box appears.

4. In the Command Button Wizard dialog box, select Form Operations in the Categories box, select Close Form in the When Button Is Pressed box, and then choose the Finish button.

5. Save the Customers form, and then view the form in Form view.

6. From the Records menu, choose Go To, and then choose New from the menu that appears.

7. Type "ABCDE" (without the quotation marks) in the Customer ID field, and then close the form. Note that the following error message appears:

         Field 'Customers.Company Name' can't contain a null value.
   

   This is expected because the Company Name field must contain a value.

8. Choose the OK button. Note that the following error message appears as expected:

         The record being edited can't be saved. If you close the form, the
         changes you've made to the record will be lost. Close anyway?
   

9. Choose the Cancel button, and then choose the Close button that you created with the Command Button Wizard. Note that the form closes without an error message. However, the new record with Customer ID "ABCDE" is not processed.

REFERENCES