Link to home
Avatar of Worcse
WorcseFlag 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
Avatar of clarkscott
clarkscott
Flag of United States of America image

Use the AFTER UPDATE event of the phone number textbox.

Scott C
ASKER CERTIFIED SOLUTION
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
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

Avatar of 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
Avatar of 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
Not quite sure, but try AfterUpdate.
Avatar of Worcse

ASKER

Did that... see my comment above...
Avatar of 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
Avatar of Worcse

ASKER

Thank you for the help