Intercept field contents before ACCESS checks if it proper format

Question

I need to be able to intercept and analyze the content of a Currency field BEFORE Access tries to validate that the data in the field is the proper format.  Specifically I need to be able to enter something like "7.94 / 3"  and have $2.65 displayed and saved in the field.  I know how to do the parsing of the text and the math  but I just need to know how to capture the string.  Maybe if I knew the error code for the message ACCESS displays I could stop the message and capture the string even then in which event would I place the error trap code?  

Answer 1

Hi Steve,

You can trap most system errors through the Form's Error event. Hope that helps...

Answer 2

If I were going to do something like this, I would probably *display* an unbound text box, load it from a hidden bound text box (or even directly from the form's recordset) in the Current event, and use the unbound text box's AfterUpdate event to evaluate the entry, and assign the result to both the unbound text box and the hidden, bound text box or field.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 3

Using the unbound text box is certainly a valid solution and it is how I solved this issue a few years ago.  What I would like to do now is take advantage of Access's internal data error checking and only execute my 'change' code if a data input error occurs.

What I have so far is:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strWork As String

If DataErr = 2113 And Me.ActiveControl.Name = "txtAmount" Then
    strWork = Me.ActiveControl
    MsgBox DataErr & "   " & Me.ActiveControl.Name & "   " & strWork
    Response = acDataErrContinue
End If

The problem is that I can't seem to capture the value shown in "txtAmount" at this time.  It is not the value OR the oldvalue.  I need to capture the 'current displayed text' of the textbox. 

Answer 4

Hi,

Assuming the error event is firing immediately as soon as the user enters the wrong type of data, then you could try replacing Me.ActiveControl with Screen.ActiveControl

Hope that helps...

Answer 5

Using the unbound text box is certainly a valid solution and it is how I solved this issue a few years ago.  What I would like to do now is take advantage of Access's internal data error checking and only execute my 'change' code if a data input error occurs.

Hi Steve,

I you want to accept "7.94 / 3" as input for a control bound to a numeric field, then you will get an Access error before you can further analyse the input.

To have better control you can use an unbound control, or disable the Format property of the control, and use the BeforeUpdate event of the control.

In this event you first check whether the input is numeric. If yes, then accept the value.

If no, you can check if   Eval(<control>)   gives a meaningful value. This handles e.g. 7.94 / 3, but also 3 * 2, etc.

If yes, eventually round the value and accept is, else you must check for other rules, or tell the user that the input is invalid.

Personally I would not rely on the Access errors to decide what to do next.

Imb.

Answer 6

Thanks DB guy,

Screen.ActiveControl  has the same result as Me.activeControl.   The error is not firing after the first non-numeric character but after hitting the tab key, enter key, or moving the mouse to any other control.  The focus never changes because after the error routine ends the cursor is still where it was at the right end of the active control.

Answer 7

If it's just one field, you might consider just adding data validation on it perhaps by using its BeforeUpdate event instead. Just a thought...

Answer 8

I certainly appreciate all the replies to my original post but so far we have not been able to 'nail it'.  I have found several posts on various sites that seemed to imply that something like 'MsgBox Me.Amount' would display the contents of the field Amount but what is displayed is the contents of the last VALID entry.  If I type yui in a textbox bound to a Currancy field named Amount in a table the form 'on error' event is fired as soon as I try to move the focus to another field via Tab or Mouse.  This is normal and expected behavior.  The problem is that I can't seem to see/display/view or change the text yui to a numeric value from within the On Error routine.

Below is the code for the On Error event.

------------------------------------------------------------------------------------------------------------------

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

    MsgBox "DataErr:  " & DataErr _
            & vbCrLf & vbCrLf & "Me.ActiveControl.Name:  " & Me.ActiveControl.Name _
            & vbCrLf & "Me.ActiveControl:  " & Me.ActiveControl _
            & vbCrLf & vbCrLf & "Screen.ActiveControl.Name:  " & Screen.ActiveControl.Name _
            & vbCrLf & "Screen.ActiveControl:  " & Screen.ActiveControl
   
    Response = acDataErrContinue
 
End Sub

--------------------------------------------------------------------------------------------------------------------

What that code produces when I type 'yui' in the textbox and hit the tab key is shown below:

What I expected the MsgBox to display is 'yui' since that is the text that triggered the Form On Error event. In this example the 999 is the value of the last valid entry in this field.  Where does access store the 'yui' text to test for a valid value???

Thanks for your help and all suggestions are appreciated.

Answer 9

What I expected the MsgBox to display is 'yui' since that is the text that triggered the Form On Error event. In this example the 999 is the value of the last valid entry in this field.  Where does access store the 'yui' text to test for a valid value???

Hi Steve,

What you see in the error message is the 'Value' property of the control: it still has the "current" value. While entering data in the control the 'Text' property of the control is filled. Only after validation the value of the 'Text' property is assigned to the value of the 'Value' property.

In this validation first is checked whether the 'Text' value is conform the format of the control, such as a date field must be a Date type, and a numeric field must be a Numeric type, and eventually an error generated as you experience.

Next you can do further validation in the BeforeUpdate event. In case of non-acceptance you can set the Cancel-flag, to leave the focus in the control, and the 'Text' can be modified further. Else the typed value is accepted and assigned to the 'Value' property of the control.

Imb.

Answer 10

Steve -

As Imb-hb has already pointed out, there is a difference between the .Text property of a control and the .Value property. The .Text property returns the actual text that the control is displaying, while the .Value property -- which is the default property of a data control such as a text or combo box -- returns the value of the control.  The .Text property of these controls is always a String, while the data type of the Value property is either Null or the data type of the field to which it is bound (if it is a bound control), or String if the control is unbound.

The .Text property is only available when the control has the focus, while the .Value property is available at any time.  In your code for the Error event, since the control has the focus, the .Text property would be available, and would show the actual text you typed into the control.  In some other events, you would be able to change the .Text property of the control, so long as the control has the focus.

However, you will find that you can't assign to either the .Text or .Value property of the control in the form's Error event, in this case where the error is raised by the initial validation Access is doing on the entry.  When data is entered in a control, you can't change the control until the processing Access does behind the scenes has reached the AfterUpdate event.  Until then, Access has the control locked, and you can't change it.  An error will be raised if you try.

Out of interest, I tried a number of ways to work around this restriction while still trapping the error in the form's Error event, and I couldn't find one. I was able, for example, to trap the error, evaluate the expression from the .Text property, and save it in a module-level variable for later assignment in the control's AfterUpdate event; however, since the text in the control was still invalid, I was unable to make the control's AfterUpdate event fire without forcing the user to make another entry in the control.

I think possibly it could be done, as a kludgey workaround, by setting and unsetting a TimerInterval of 1 millisecond and using the form's Timer event to correct the entry in the control.  However, that seems far too elaborate a process to recommend itself to me, even if it would work; especially when there is a much simpler way to do it, which I've already recommended.

That way, which you've ignored so far, is to use an unbound text box on the form to represent the underlying field, instead of binding the text box to the field.  In the form's Current event, set the value of the text box to the value of the field.  In the text box's BeforeUpdate event, validate the entry and cancel the event if the entry is neither a valid value for the field nor a valid expression.  In the text box's AfterUpdate event -- which won't fire if you cancel the BeforeUpdate event -- evaluate the entry and assign the result to the field.

That approach should work fine.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 11

That way, which you've ignored so far, is to use an unbound text box on the form to represent the underlying field, instead of binding the text box to the field.  In the form's Current event, set the value of the text box to the value of the field.  In the text box's BeforeUpdate event, validate the entry and cancel the event if the entry is neither a valid value for the field nor a valid expression.  In the text box's AfterUpdate event -- which won't fire if you cancel the BeforeUpdate event -- evaluate the entry and assign the result to the field.

Hi Dirk and Steve,

In my opinion the evaluation of the data entry in AfterUpdate event is too late, it should be done in the BeforeUpdate event.

But there is an almost identical way to prevent the errors generated by the initial validation of Access, and so use your own error messages. In the Enter event of the (editable) control you can set the ControlSource = "". In fact, make the control temporarely unbound. The rest is the same as Dirk describes in the above paragraph.
After doing all the tests, including  the tests Access normally does on forehand (e.g. an entry that should be a date, is it really a valid date?). In the AfterUpdate or Exit event of the control you can then restore the original value of the ControlSource.

Imb,

Answer 12

In my opinion the evaluation of the data entry in AfterUpdate event is too late, it should be done in the BeforeUpdate event.

I don't see any particular reason for that, though in the case of an unbound text box in a scenario like this, it's fairly arbitrary.  However, to preserve the logical sequence of events, where the BeforeUpdate event is used for validation and the AfterUpdate event is used for responding to a valid entry, it makes the most sense to me to use the BeforeUpdate event to determine whether the user's entry is acceptable, either as a direct value entry or as an expression, and then apply the actual update to the field in the AfterUpdate event.  One could also make a case for performing both of those functions in the BeforeUpdate event, freeing up the AfterUpdate event for other operations that would be independent of this special expression-evaluation feature.  But since the text box is unbound, either event, or a combination of the events, could be used.

But there is an almost identical way to prevent the errors generated by the initial validation of Access, and so use your own error messages. In the Enter event of the (editable) control you can set the ControlSource = "". In fact, make the control temporarely unbound. The rest is the same as Dirk describes in the above paragraph.
After doing all the tests, including  the tests Access normally does on forehand (e.g. an entry that should be a date, is it really a valid date?). In the AfterUpdate or Exit event of the control you can then restore the original value of the ControlSource.

Yes, that would work.  You'd be trading the work of loading the text box in the Current event for the work of unbinding and rebinding the text box in the Enter and Exit events.  It would have the advantage of isolating the quirkiness of the text box to just when the control has the focus, and would let the text box readily reflect data changes made external to this setup.  If it works in practice, I think it's a good idea.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 13

>In my opinion the evaluation of the data entry in AfterUpdate event is too late, it should be done in the BeforeUpdate event.

I don't see any particular reason for that, though in the case of an unbound text box in a scenario like this, it's fairly arbitrary.  However, to preserve the logical sequence of events, where the BeforeUpdate event is used for validation and the AfterUpdate event is used for responding to a valid entry, it makes the most sense to me to use the BeforeUpdate event to determine whether the user's entry is acceptable, either as a direct value entry or as an expression, and then apply the actual update to the field in the AfterUpdate event.

Hi Dirk,

I think I misunderstood evalution. For my feeling it was close to validation.

Imb.