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

 

[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

 

[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