Solved Copy and paste telephone numbers.

[Eljefegeneo]

I am trying to copy a telephone number from a web page and paste it into my form. The text field on the form is the standard ten digit USA telephone input mask. !\(999") "000\-0000;0;_

But when I try to copy a telephone number, 212-555-1212 or (212)-555-1212 or 212.555.1212 only the first three digits are pasted into the text box. Any suggestions?

 

[June7]

It works for me. The first hyphen you show in sample numbers is not in the mask. When I tested with the hyphen in value, doesn't work.

Do you have ValidationRule set with some condition?

Just typing would be faster than copy/paste.

 

[NauticalGent]

If you paste it into Notepad, do you get the same results?

If you do, the issue is website, if not the problem is your DB

 

[Eljefegeneo]

It works for me. The first hyphen you show in sample numbers is not in the mask. When I tested with the hyphen in value, doesn't work.

Do you have ValidationRule set with some condition?

No validation rule, just the input mask.

 

[Eljefegeneo]

If you paste it into Notepad, do you get the same results?

If you do, the issue is website, if not the problem is your DB

Pasting into the notepad works fine. It just doesn't go into the text field. It will if I paste it to an unbound text box and then use code to update the Telephone text box.

 

[MajP]

It is because you are trying to past the literal' "-" and "(" and ")" instead of just a number.

 

[Eljefegeneo]

Yes, I now understand. I fixed it in my usual half ass way. Put in an unbound text box (like a note pad) only visible if nothing in the Telephone control, paste the number to the unbound text box and then on double click it enters into the Telephone control and the unbound text box disappears.

 

[June7]

This works for me: copy (222) 222-2222 and paste that. But as I said, including another hyphen fails.

 

[Eljefegeneo]

My way seems to work except, always the except, if you paste 8005551212 and double click it enters 805551212 and not the input mask format. Likewise for 800.555.1212, gets pasted as 800.555.1212. Most other formats work fine. Going to have to figure out how to get these odd formats into the input mask format.

 

[Eljefegeneo]

So, finally I came up with what I think is a decent solution. I set up the unbound text box to be visible if there is no telephone number listed. Then I paste the telephone number in any format into it and use Double Click event to populate the telephone control.

First I used an extended replace function:

Function MyReplace(ByVal sInput As String) As String

MyReplace = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(sInput, "_", ""), "@", ""), "$", ""), "%", ""), "!", ""), ".", ""), ")", ""), "(", ""), "-", ""), " ", "")
End Function

Then I used the following code to format and paste the telephone number into the Telephone text box.

 Me.Refresh
Dim sInput As String
Dim nInput As String
sInput = Me.txtel
     nInput = MyReplace(sInput)
            Me.Telephone = Format(nInput, "(###) ###-####")
                     Me.txtel.Visible = False

 

[MsAccessNL]

Great that you solved your problem! You inspired me to turn your code into a general character remove function:
sRemove can hold any length of characters like ("_,@,%")

Public Function MyRemover(sInput As String, sRemove As String) As String
    Dim i As Integer, s As String
    Dim aVal As Variant
    Dim arrTemp() As String
    
    s = sInput
    arrTemp = Split(sRemove, ",")
    
    For Each aVal In arrTemp
        s = Replace(s, aVal, "")
    Next
    
    MyRemover = s
End Function

 

[MsAccessNL]

For who is interested, the GetNumber function:

Public Function GetNumber(sNumber As String) As String
    Dim s As String, sTemp As String
    Dim i As Integer
    'x = " (212)-555-12.12) "

    For i = 1 To Len(sNumber)
        sTemp = Mid(sNumber, i, 1)
        If IsNumeric(sTemp) Then s = s & sTemp
    Next
    GetNumber = s
    
End Function