Runtime error 2115 at Access 2013

Question

My Access database is linked to my SQL Server 2008. I'm trying to validate if the value entered into a textbox field (called diverNo) is a number (only numbers are allowed in this field). I want it to be validated as soon as the user leaves the field, and if the user tries to put any character which is not a number, the field will be cleaned:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 2113 Then
Response = acDataErrContinue
MsgBox ("Only numbers are allowed in Diver Number")
Me.diverNo = "" ' <- this line cause the error

Exit Sub

End If
End Sub

I get Runtime-error 2115 which states "The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Office Access from saving the data in the field."

Any suggestions to fix this?

Answer 1

Calling the Undo method and setting Cancel to True in the BeforeUpdate handler seems to work.

Private Sub diverNo_BeforeUpdate(Cancel As Integer)

If IsNumeric(Me.diverNo) = False Then
    Cancel = True 'stops the record from being saved.
    Me.diverNo.Undo 'only the control itself is affected.

    MsgBox ("Please only enter a number")
End If

End Sub

BeforeUpdate is good if you also want to evaluate an old vs new value being entered into a field (for a bound field). This is lost in the AfterUpdate method, as the record has already updated.

Answer 2

I would create an AfterUpdate event on the diverNo field, and check the value:

Private Sub diverNo_AfterUpdate()

If Not IsNumeric(Me.diverNo) Then

Me.diverNo.Undo or Me.diverNo = Null

End If

End Sub

With your current code, setting the Me.diverNo = "" is attempting to save a string value in the numeric field. You may also want to consider creating a ValidationRule for the diverNo control.