Stop from saving when closing a form

[human_anomaly]

I have forms that are trying to save when they are being closed and with null values it displays an error message. I want to stop it from saving when closing the form. Normally this wouldn't be a problem I could create a custom button that closes the form without saving using a code such as: DoCmd.Close acForm, "F_Orders", acSaveNo
but the user doesn't want a custom button they want to close it using the "x" close button on the form in the upper right hand corner. Is there any way to edit this action so it doesn't try to save when it is clicked?

Thanks,
Ben Fyvie

 

[Rich]

acSave No will not prevent records from being saved. Search here for BeforeUpdate or validation

 

[human_anomaly]

I tried using the beforeupdate method but the problem is that is doesn't ever run. I forgot to tell you that the database is a subscriber to an sql database, that is were I am getting the Null error from when I try to close the form. Any other ideas. I've tried numorous methods but none of them ever seem to run before trying to update with the sql server. Any ideas?

 

[human_anomaly]

I found that it does stop at beforeInsert() is there any way to stop the insert from occuring so that I can manually insert the new record only when the form is completely filled?

 

[Mile-O]

You should use the BeforeUpdate event to determine whether you want to save your record.

To clear all the recent changes :

If Me.Dirty Then Me.Undo

 

[human_anomaly]

The insert wasn't for the sql server it was for the form itself so it could only stop things from being inserted into the form itself and not into the sql server.

 

[Mile-O]

What's SQL Server got to do with anything here?

 

[human_anomaly]

Access is not saving its data locally it is saving it out on the SQL server.

 

[human_anomaly]

ok i have fixed on of the error messages from displaying by using:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Class.Value <> "" And TxtLName.Value <> "" And TxtFName.Value <> "" Then
Cancel = False
Else
Cancel = True
End If
End Sub

so access no longer displays the error messge of:
"Cannot insert the value NULL into column 'Student', table'Medic.dbo.StudentTbl'; column does not allow nulls. INSERT fails."

The problem now is that is is still displaying the message box:
"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 you made will be lost. Do you want to close the database object anyway? <Yes> <No>"

I would like to stop access from showing this message and automatically close the form. Is that possible?

 

[human_anomaly]

Solution

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Class.Value <> "" And TxtLName.Value <> "" And TxtFName.Value <> "" Then
Cancel = False
Else
Cancel = True
End If
End Sub


Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2169 Then Response = True
End Sub

This did exactly what I was looking for!