Convert form field data OnExit

[magmike]

I could use this advice in a few different scenarios, but for example:

In the telephone field on the form, I use an Input Mask, but do not
store the symbols. However, I have a source that I frequently use that
displays the phone numbers like "615.555.1212".

When I paste this into the phone form field, on exit it would
naturally create an error. So I have to write it down and enter it as
plain numbers to avoid error. Even when a source will use the typical
symbols "(615) 555-1212" they don't always paste in successfully, as
some may have a space or not a space or something minor that doesn't
translate correctly in the input mask.

Is there a way to either:

(1) onClick or onEnter into the phone field, eliminate all symbols
so that just the actual numbers paste in,

or

(2) onExit, convert the data to fit within the input mask
constraints.

or

(3) Is there another, better way than either of those?

Thanks in advance,

magmike

 

[Allen Browne]

Magmike, I think the problem here is that you are using input masks. Would
you consider dropping the mask completely?

IME, input masks have more negatives than positives. Not only do they have
the problems you describe, but they serve to slow down a good data entry
operator. Leave a digit out, and you can't just back up and insert it: you
are entering the number again. Additionally, for phone numbers, the input
mask tends to be a barrier for entering international numbers, mobiles, and
other special kinds of numbers. IMHO, you're better without it.

You may be able to use the Format property of the text box to *display* the
formatting you want (brackets, spaces and dashes), though again this is not
appropriate for all kinds of numbers. Using a separate field for the area
code might also be appropriate.

It's easy enough to use the AfterUpdate event procedure of the control to
strip out any non-digit characters:
1. Paste the function below into a standard module (so you can call it from
any form.)

2. Set the AfterUpdate event procedure of your text box (named Phone in the
example below) to:
[Event Procedure]
Then click the Build button beside that property.
Access opens the code window.
Paste this line in between the other two:

Private Sub Phone_AfterUpdate()
Me.[Phone] = StripNonDigit(Me.[Phone].Value)
End Sub

Public Function StripNonDigit(varIn As Variant) As Variant
Dim i As Integer
Dim iChar As Integer
Dim strOut As String

If Not IsError(varIn) Then
For i = 1 To Len(varIn)
iChar = Asc(Mid(varIn, i, 1))
If iChar >= vbKey0 And iChar <= vbKey9 Then
strOut = strOut & Chr$(iChar)
End If
Next
End If

If strOut <> vbNullString Then
StripNonDigit = strOut
Else
StripNonDigit = Null
End If
End Function