Avatar of Worcse
Worcse
Flag for United States of America asked on

Creating a Function

On a form I have many different phone numbers.
Currently I am using an AfterUpdate code for each phone number (see attached code)

How would I make a Function from this code so that I could call it after I enter any phone number?

The code checks the length of the number entered and either formats the number if it is the correct length or gives a message box if the length is incorrect.

If the length is incorrect it will return the focus to the phone number just entered

Thanks as always for your assistance!

Worcse
PhoneCode.txt
Microsoft OfficeMicrosoft Access

Avatar of undefined
Last Comment
Worcse

8/22/2022 - Mon
clarkscott

Use the AFTER UPDATE event of the phone number textbox.

Scott C
ASKER CERTIFIED SOLUTION
Hamed Nasr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Helen Feddema

Here is my standard phone number formatting code (it formats U.S. and Canadian phone numbers, and leaves others as is):

Public Sub FormatPhone(txt As Access.TextBox, strPhoneNumber As String)
'Created by Helen Feddema 12-May-2006
'Modified by Helen Feddema 9-Aug-2006

On Error GoTo ErrorHandler

   strTitle = "Format problem"
   intLength = Len(strPhoneNumber)
   
   Select Case intLength
      Case 10
         If strPhoneNumber Like "##########" Then
            strPhoneNumber = Format(strPhoneNumber, "(###) ###-####")
            txt.Value = strPhoneNumber
            GoTo ErrorHandlerExit
         Else
            strPrompt = "Phone number incorrectly formatted"
            MsgBox strPrompt, vbExclamation, strTitle
            GoTo ErrorHandlerExit
         End If
         
      Case 12
         If strPhoneNumber Like "###-###-####" Then
            strPhoneNumber = Format(StripChars(strPhoneNumber), _
               "(###) ###-####")
            txt.Value = strPhoneNumber
            GoTo ErrorHandlerExit
         Else
            'Length = 12 and not formatted correctly
            strPrompt = "Phone number incorrectly formatted"
            MsgBox strPrompt, vbExclamation, strTitle
            GoTo ErrorHandlerExit
         End If
      
      Case 13
         If strPhoneNumber Like "(###)###-####" Then
            strPhoneNumber = Format(StripChars(strPhoneNumber), _
               "(###) ###-####")
            txt.Value = strPhoneNumber
            GoTo ErrorHandlerExit
         Else
            'Length = 13 and not formatted correctly
            strPrompt = "Phone number incorrectly formatted"
            MsgBox strPrompt, vbExclamation, strTitle
            GoTo ErrorHandlerExit
         End If
      
      Case 14
         If strPhoneNumber Like "(###) ###-####" Then
            'Formatted correctly
            GoTo ErrorHandlerExit
         Else
            'Length = 14 and not formatted correctly
            strPrompt = "Phone number incorrectly formatted"
            MsgBox strPrompt, vbExclamation, strTitle
            GoTo ErrorHandlerExit
         End If
   
      Case Else
            strPrompt = "Phone number incorrectly formatted"
            MsgBox strPrompt, vbExclamation, strTitle
            GoTo ErrorHandlerExit
      
   End Select

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

It is called like this (the StripChars function removes parentheses, spaces and dashes from the entered phone numbe, which means you don't have to instruct users in proper formatting -- a lost cause if ever there was one!):
   strRawPhone = StripChars(Me![txtPhone].Value)
      Call FormatPhone(txt, strRawPhone)

Public Function StripChars(strText As String) As String
'Strips a spaces and parentheses from a text string
'Created by Helen Feddema 10-15-97
'Modified by Ruud H.G. van Tol 6-18-99
'Modified by Brad Beacham 6-Feb-2005
'Last modified by Helen Feddema 7-Feb-2005

On Error GoTo ErrorHandler

    Dim strTestString As String
    Dim strBadChar As String
    Dim i As Integer
    Dim strStripChars As String

    strStripChars = " ()-"
    strTestString = strText

    For i = 1 To Len(strStripChars)
        strBadChar = Mid(strStripChars, i, 1)
        strTestString = Replace(strTestString, strBadChar, vbNullString)
    Next

    StripChars = strTestString

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

Worcse

ASKER
hnasr / Helen - thank you both
give me moment to try both of these solutions
I will let you know which one works best for me
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Worcse

ASKER
hnasr - i am working with your code

Sorry about that...

I have attached an error sreen I am getting

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If I move your code into the AfterUpdate it works fine...
Howerever, if the wrong lenghth is entered the message box will appear (which is good) but
when you click ok the focus will tab to the next text box and will delete any information entered into the phone text box i was just in

Ideally, after clicking okay on the messate box I would like to keep any numbers already entered into that text box and rather than automatically tab to the next text box... keep the focus on that box
error.docx
Hamed Nasr

Not quite sure, but try AfterUpdate.
Worcse

ASKER
Did that... see my comment above...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Worcse

ASKER
hnasr
If I move your code into the AfterUpdate it works fine...
Howerever, if the wrong lenghth is entered the message box will appear (which is good) but
when you click ok the focus will tab to the next text box and will delete any information entered into the phone text box i was just in

Ideally, after clicking okay on the messate box I would like to keep any numbers already entered into that text box and rather than automatically tab to the next text box... keep the focus on that box
Worcse

ASKER
Thank you for the help