Simple Replace

[Rod]

The user uses voice recognition to input phone numbers. The voice
recognition often inserts a "-" in the phone number, i.e. the user speaks
2161234 and the voice recognition inputs 216-1234 in the field assuming that
is what the user wants - it's not.

I want to remove all non number from the INPUT_NUMBER before the update.
Here is my code:

Private Sub INPUT_NUMBER_BeforeUpdate(Cancel As Integer)
'Numbers only
If Not IsNull(INPUT_NUMBER) Then
INPUT_NUMBER = Replace(INPUT_NUMBER, "-", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, " ", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "a", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "b", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "c", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "d", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "e", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "f", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "g", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "h", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "i", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "j", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "k", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "l", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "m", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "n", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "o", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "p", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "q", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "r", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "s", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "t", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "u", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "v", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "w", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "x", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "y", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "z", "")
End If

End Sub

I receive error '-2147352567 (80020009):
The macro or function set to the BeforeUpdate or ValidationRule property for
this field is preventing Microsoft Office Access from saving the data in the
field.

Thoughts?

 

[Allen Browne]

Use the AfterUpdate event of the control when you want to change the value.

It might be easier to paste in the function below, and use:
Private Sub INPUT_NUMBER_AfterUpdate()
Me.INPUT_NUMBER = DropNonDigit(Me.INPUT_NUMBER)
End Sub

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

If Not IsNull(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
DropNonDigit = Null
Else
DropNonDigit = strOut
End If
End Function

 

[Klatuu]

I don't think you can change the value of the control in the before update
event.
Here is a way you can do it using the Change event. Substitute your names.


Private Sub Text37_Change()
If Len(Me.Text37.Text) > 0 Then
If Not IsNumeric(Right(Me.Text37.Text, 1)) Then
Me.Text37.Text = Left(Me.Text37.Text, Len(Me.Text37.Text) - 1)
Me.Text37.SelStart = Len(Me.Text37.Text)
End If
End If
End Sub

 

[Marshall Barton]

The user uses voice recognition to input phone numbers. The voice
recognition often inserts a "-" in the phone number, i.e. the user speaks
2161234 and the voice recognition inputs 216-1234 in the field assuming that
is what the user wants - it's not.

I want to remove all non number from the INPUT_NUMBER before the update.
Here is my code:

Private Sub INPUT_NUMBER_BeforeUpdate(Cancel As Integer)
'Numbers only
If Not IsNull(INPUT_NUMBER) Then
INPUT_NUMBER = Replace(INPUT_NUMBER, "-", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, " ", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "a", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "b", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "c", "") [. . .]
INPUT_NUMBER = Replace(INPUT_NUMBER, "z", "")
End If

End Sub

I receive error '-2147352567 (80020009):
The macro or function set to the BeforeUpdate or ValidationRule property for
this field is preventing Microsoft Office Access from saving the data in the
field.

The BeforeUpdate event can not change the value. It can
only accept or reject the value.

Use the AfterUpdate event instead.

You may want to consider using a code sequence more like:

Dim strClean As String
Dim k As Integer

For k = 1 To Len(INPUT_NUMBER)
If Mid(INPUT_NUMBER,k,1) Like "#" Then
strClean = strClean & Mid(INPUT_NUMBER,k,1)
End If
Next k
INPUT_NUMBER = strClean