Sanitizing number field on form

[olxx]

Hi.

I have numeric field (price) on form which sometimes needs sanitizing because values are pasted from different sources. I have working function for that, it checks if decimal separator is correct, removes any text parts (€ signs for example) etc. Problem is that i can't figure out which event should i put it. AfterUpdate fires too late and gives error if entry is not correctly sanitized. BeforeUpdate fires too early and the passes null to function if textbox is empty. Yes, i could use extra unbounded textbox for input, but seems clumsy. Any ideas or experience?

 

[CJ_London]

How about the change event? - ref the controls text property

And your beforeupdate should be referencing the text property

 

[Pat Hartman]

The problem starts with the field being defined as text rather than a number. If you define the field as currency, for example, then the form itself will prevent the entry of invalid characters.

You will have nothing but trouble using a numeric field that is defined as text.

The BeforeUpdate event of the control runs as soon as the control loses focus and is the correct event to use to validate data if you want to validate it before you move to the next control. No other event covers all bases. The AfterUpdate event runs AFTER the entry is accepted and focus is in the next control so it is too late to use to validate data. I prefer to use the form's BeforeUpdate event and validate all controls in one place. The only time I use the control's BeforeUpdate event is if I want to stop data entry immediately if the data is invalid because if the user cannot fix the data, he will not be allowed to save the record. So, this would be something like making sure that the SSN (Social Security Number) is unique for example.

The Change event runs once for each keystroke in the control so it is only used if you want to validate character by character. Also, when using the Change event, you must use the .text property to refer to the control's contents rather than the default property which is .value

 

[Pat Hartman]

I created a database and several videos to explain how to correctly do data validation.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

I suggest watching at least one of the videos before attempting to use the database so you know how the event logger works. You can test using the included forms or import your own and add the one line reference as the first line of code in any event you want logged.

 

[CJ_London]

The Change event runs once for each keystroke in the control so it is only used if you want to validate character by character.

agreed - but the OP is copy/pasting from other sources

because values are pasted from different sources.

so there is only one keystroke

 

[Pat Hartman]

Then there is no point in using the Change event. The BeforeUpdate event works on the whole field also.

 

[olxx]

Thank you all for info. Little clarification is required. Price field is already formated as currency, no invalid data cannot be entered because access will prevent it. This sanitizing process is only for automatic correction if data is pasted. Usually because there is "." instead of "," or € sign at the end and this will trigger access warning and user has to manually correct it. It is annoying and consumes time. At first i tried beforeUpdate on textbox and as it didn't work, i thought that this event will fire before data is pasted. Strange thing is that if i put breakpoint on that event, it seems beforeUpdate don't fire at all. Thank you Pat for video, but i have no audio on work pc, so i have to watch it later.
My code:

Private Sub Summa_BeforeUpdate(Cancel As Integer)

    Dim cleanValue As Variant
    cleanValue = CleanPriceInput(Me.Summa.Value)

    If Not IsNull(cleanValue) Then
        Me.Summa.Value = cleanValue
    End If
End Sub

Summa is textbox name, CleanPriceInput is function.

 

[CJ_London]

Suggest try the suggestion in post #2

And as advised in post#2 - you need to use the text property, not the value property

 

[GaP42]

Perhaps a technique that could be used is to use an unbound control to accept the pasted value and then apply the sanitizing routine. The value is then pasted into the bound control.

This can be done with the unbound control hiding under the bound control (sent to the back), but when focus is given to the bound control it passes focus to the unbound control, allowing entry of the unsanitised data (pasting). The after update event of the unbound control posts the sanitised data into the bound control. The form's before update event can then be used to do validation as Pat suggested, as the bound control's value has changed and the form is dirty.

This technique is used to allow apparent entry of data into a control displaying a calculated value. As a calculated control cannot accept entry, the hidden unbound control is used and the value applied to the desired field.

 

[arnelgp]

as advised, use the Change event of your texbox:

Private Sub Summa_Change()
    Dim cleanValue As Variant
    cleanValue = CleanPriceInput(Me.Summa.Text)

    If Not IsNull(cleanValue) Then
        Me.Summa.Value = cleanValue
        Me.Summa.SelStart = Len(cleanValue & "")
            
    End If
End Sub

 

[Pat Hartman]

And as advised in post#2 - you need to use the text property, not the value property

There are three buffers associated with controls.
1. text - this is the type into buffer. This is why when you are using the Change event, you need to refer to the control using .text or you won't see the characters as they are being typed.
2. value - this is the current buffer. This is what you are seeing the form display for existing data or after data entry for the control is complete. When typing is complete, the contents of the .text buffer are transferred to the .value buffer and Access' editing takes place. Then your BeforeUpdate event runs
3. Oldvalue - this is the value of the saved data. It is empty when the form is on a new record until the record is saved for the first time. It is also empty if the "saved" value is null. The contents of the .value buffer is transferred to .OldValue between the form's BeforeUpdate and AfterUpdate events.