Hi all. I'm using a variation of a bit of VBA code that I found through some Google sleuthing, but I can't seem to figure out why it only works some of the time.
Basically, I want a custom error message to appear instead of the somewhat cryptic one my end users would get if they left a required field blank on a form, and tried to close it (where Access alerts them to the fact that the record cannot be saved). The code works perfectly when I try using it with an EXISTING record: that is, if they press NO (don't close without saving), they return to the form, and if they press YES (continue close without saving), the form closes without saving.
BUT, if this is a new record, it doesn't work. If they try to close a form for a new record with a required field blank, the custom error message comes up, but if they press NO (which should return to the form), it closes without saving. If they press YES (should close without saving), it says "Run-time error 2501: the close action was canceled" and then it still closes without saving.
Here is my code. Could someone please point me in the right direction on what needs changing? THANK YOU
Code:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
'If an error occurs because of missing data in a required field
'display our own custom error message
Const conErrRequiredData = 3314
If DataErr = conErrRequiredData Then
If MsgBox("Access can't save the data you've entered because at least 1 required field was not filled in. Required fields appear in bold. Are you sure you want to close without saving?", vbYesNo + vbDefaultButton2, "Cannot Save") = vbYes Then
Response = acDataErrContinue
DoCmd.Close acForm, "Resources", acSaveNo
Else
Response = acDataErrContinue
End If
Else
'Display a standard error message
Response = acDataDisplay
End If
End Sub