Trapping Form Error 2113 and reverting control

[GK in the UK]

Some Access error messages fire the Form_Error event.

When I paste some garbage into a numeric format unbound field, say txtPrice, that had valid data, I can trap the error in Form_Error. I can see txtPrice.text still contains the original, valid input.

What I would like to do is revert txtPrice.text if Form_Error fires my custom message. Is there a way to do this? I tried assigning it in Form_Error (txtPrice = txtPrice.text) but Access complains 'the ... validation ... is preventing Access from saving the data in the field'

 

[The_Doc_Man]

Typically, "revert" is done by something like:

Me.<controlname>.Value = Me.<controlname>.OldValue

However, this solution ONLY works for bound controls because unbound controls generally do not have an .OldValue property.

 

[arnelgp]

Or you can use:

Me.control.undo

 

[The_Doc_Man]

Arnel, does that work if the control is unbound?

I have used .Undo for bound controls - anything with a .Rowsource can be undone and anything that is bound to a field in the form's .Recordsource, too. But there have been times when it didn't work for me. Never quite figured out why but it was always for a combo or list box when it failed.

 

[Pat Hartman]

You can't use the .text property for this. In Access VBA, the default control property is .value and is normally omitted so your field would be referenced as Me.txtPrice
That will get you intellisense. The .text property is normally only used when the control has the focus. In newer versions of Access, you seem to be able to reference the .text property from other events than the on Change event but I don't believe you can set it.

If nothing else works, you might be able to use the Control's AfterUpdate event and set the field to null.

 

[missinglinq]

Testing shows that accessing the OldValue Property of an Unbound Control always returns the Current Value...not any previous value.

Testing also shows that

Me.control.undo

does not work for an Unbound Control.

Linq ;0)>

 

[isladogs]

The following works for me:

Remove the Number format from your unbound textbox as this is preventing the Before_Update event being used

Then add this code to your textbox / form

Option Compare Database
Option Explicit

Dim lngValue As Long

Private Sub Text0_BeforeUpdate(Cancel As Integer)
    If IsNumeric(Me.Text0) Then lngValue = Me.Text0
End Sub

Private Sub Text0_AfterUpdate()
    If Not IsNumeric(Me.Text0) Then Me.Text0 = lngValue
End Sub

The before update event saves the last valid entry as lngValue
The after update event checks for a number value & if not found reverts to the last saved value

NOTE: If the first entry made is invalid the code replaces it with 0

 

[arnelgp]

Sorry missinglinq i just tested it abd it reverts.
Here is how i tested.
Put two unbound textbox.
The first one has General Number format. This is the textbix to test.
On the load event if the form, i set the value of the first textbox to 123.
I added cide to the form's Error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Me.firstTextbox.Undo
Response=acDataErrContinue
End Sub

You may try typing string text to the textbox
Second test change the value to any number move to the second textbox then back again and enter string text again.

 

[GK in the UK]

arnelgp seems to have it. Me.control.undo does indeed work for my unbound control. Here's an extract from my code which pops up the error and recovers the old, valid value.


missinglinq, are you the one and same member of utteraccess ? because that's where I found the code for dealing with a form error (thank you!). As you can see I just added the .Undo line and it worked. I had been trying to assign txtQuantity = txtQuantity.OldValue in there but Access popped up the message saying it couldn't save the data.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    ' [URL]http://www.utteraccess.com/forum/index.php?showtopic=2023962[/URL]
    ' traps control errors which fire before any event
    
    If DataErr = 2113 Then 'Data entered does not match datatype of Control Source
        Select Case Screen.ActiveControl.Name
            Case "txtQuantity"
                Response = MsgBox("DataErr = 2113 - Invalid numeric format", vbExclamation, "Invalid Numeric Format")
                Response = acDataErrContinue
                txtQuantity.Undo

txtQuantity is an unbound text control.

Once again thanks to all for contributions.

edit: Posts 7 and 8 posted whilst I was typing !

 

[isladogs]

When I tested your scenario, the form error was 0 not 2113.
No matter what I did, I couldn't trigger 2113.

Whilst I understand the principle of the Form_Error event, I prefer to handle the errors in the control where they occur. This is particularly true for a form with a large number of controls

 

[GK in the UK]

Ridders, yes you're right, I'd figured out it was due to the format property. I was using the IsNumeric function for some controls but that was only working when I didn't have a number format, then I had issues with 123.40 entered as a monetary value appearing as 123.4 which isn't very tidy. Your code snippet will work for me for some controls, though.

 

[missinglinq]

Most strange...I can't get Undo to work on an Unbound Control no matter what I do!

 

[isladogs]

Most strange...I can't get Undo to work on an Unbound Control no matter what I do!

After reading your comments, I set up the form exactly as arnelgp described
Using A2010, it worked exactly as arnel intended

However, I still prefer my approach

 

[EngOx]

I had a similar problem I would put a -ve sign into the number field on a Sub Sub Form, the realize I need to look at something on the main form and I would click on the main form and all of a sudden I was stuck in a "The value you entered isn't valid for this field" infinite loop. I had to shut down Access from task manager.

This validation event fires before all the control events as previously mentioned. So I added a error checking to all 3 levels of forms and it worked.

To clear the contents of the control and return to the control use Me.ActiveControl.Undo
Here is one of my error code routines.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    
    If DataErr Then
         MsgBox "Form: " & Me.Form.Name & vbCr & _
                        "(Company Information Error)" & vbCr & _
                        "error: " & DataErr & "   Desc: " & AccessError(DataErr)
         Me.ActiveControl.Undo
         Response = acDataErrContinue
    End If
End Sub