Microsoft KB Archive/131813

= ACC: Edits Not Processed with Close Action on Form =

Article ID: 131813

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q131813



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



SYMPTOMS
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

For example, if you choose the Command Button Wizard to create a Close button on a form, the following Visual Basic (or Access Basic in Microsoft Access 2.0) Sub procedure is generated.

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

To force a record to be saved before the Close action is run, insert the RunCommand method (or DoMenuItem method) immediately before the DoCmd Close line in the Sub procedure. This will cause Microsoft Access to prompt you with a message as to why the record cannot be saved.

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.



In Microsoft Access 7.0 and 97
 Open the sample database Northwind.mdb, and open the Customers form in Design view. Make sure that the Control Wizards button is selected in the Toolbox before creating the command button. Add a command button to the form.</li> 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.</li> Save the Customers form, and then view the form in Form view.</li> On the Edit menu, click Go To, and then click New Record on the Go To submenu.</li> Type ABCDE 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.

</li>  If you then click OK, you will receive the following additional message:

<pre class="fixed_text">     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? </li> 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.</li></ol>

In Microsoft Access 2.0
<ol> Open the sample database NWIND.MDB, and open the Customers form in Design view.</li> Make sure that the Control Wizards is selected (has a check mark) on the View menu.</li> Add a command button to the form. Note that the Command Button Wizard dialog box appears.</li> 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.</li> Save the Customers form, and then view the form in Form view.</li> From the Records menu, choose Go To, and then choose New from the menu that appears.</li> Type ABCDE 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.</li> 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?

</li> 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.</li></ol>

<div class="references_section">