ACC2000: How to Change Area Codes Based on Telephone Number Prefix (210579)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

To create the function, follow these steps:

1. Create the following table, which will contain the phone number prefix to search for and the new area code:

      Table: Area Codes To Change
      ---------------------------
      Field Name: Prefix
         Data type: Text
      Field Name: Area Codes
         Data type: Text
   					

2. Type the phone number prefixes and area codes in the Area Codes To Change table. For example:

      Prefix      Area Codes
      ----------------------
      635            207
      634            208
   					

3. Open a module or create a new one, and type the following code:

   '*************************************************************
   'Declarations section of the module.
   '*************************************************************
   
   Option Explicit
   
   '=============================================================
   ' Create the following ChgAreaCode() function in the Module
   ' The following function will initialize:
   ' - A variable for the database object.
   ' - Variables for the table objects and several other objects.
   ' - Sets the database object to the opened database.
   ' - Opens table "Area Codes To Change" and tPhName.
   ' - The action of this function is to locate the phone number
   '     entries with certain prefixes and then change the
   '     corresponding area code. 
   '
   '     NOTE: This function operates on numbers stored with the 
   '     following format "(425) 635-7050". The table name and 
   '     field name containing the phone number must be passed
   '     as arguments.
   '=============================================================
   
   Function ChgAreaCode (tPhName, fldPhone)
      Dim PhoneDB As DAO.Database
      Dim tPhone As DAO.Recordset, tPrefix As DAO.Recordset
      Dim PCount%
      Dim i%
      Dim tPrefixName$
      Dim Prefix$
      Dim SpacePos%
      Dim HyphenPos%
      Dim PrefixLen%
      Dim PrefixToFind$
      Dim AreaCode$
      Dim Lastfour$
      tPrefixName$ = "Area Codes To Change"
   
      If tPhName = "" Or fldPhone = "" Then Exit Function
   
      Set PhoneDB = CurrentDB()
      Set tPrefix = PhoneDB.OpenRecordset(tPrefixName$)
   
      tPrefix.MoveLast
      PCount% = tPrefix.RecordCount
      tPrefix.MoveFirst
      ReDim PrefixArray$((PCount% - 1), 1)
   
      For i% = 0 To PCount% - 1 Step 1
         PrefixArray$(i%, 0) = tPrefix![Prefix]
         PrefixArray$(i%, 1) = tPrefix![Area Codes]
         tPrefix.MoveNext
      Next i%
      tPrefix.MoveFirst
      tPrefix.Close
   
      Set tPhone = PhoneDB.OpenRecordset(tPhName)
      Do Until tPhone.EOF
         SpacePos% = InStr(1, tPhone(fldPhone), " ")
         HyphenPos% = InStr(SpacePos% + 1, tPhone(fldPhone), "-")
         PrefixLen% = (HyphenPos% - SpacePos%) - 1
         PrefixToFind$ = Mid(tPhone(fldPhone), SpacePos% + _
                            1, PrefixLen%)
   
         For i% = 0 To PCount% - 1 Step 1
            If PrefixArray$(i%, 0) = PrefixToFind$ Then
               AreaCode$ = PrefixArray$(i%, 1)
               Prefix$ = Mid$(tPhone(fldPhone), 7, 3)
               Lastfour$ = Right$(tPhone(fldPhone), 4)
               tPhone.Edit
               tPhone(fldPhone) = "(" & AreaCode & ") " _
                                     & Prefix & "-" & Lastfour
               tPhone.Update
            End If
         Next i%
   
         tPhone.MoveNext
      Loop
      tPhone.Close
      PhoneDB.Close
   End Function
   					

When you use this function, you pass tPhName, the name of the table that contains the number to change, as a string. Then you pass fldPhone, the field name that contains the number to change, as a string.

Example

To call this function from the Immediate window, type the following line, and then press ENTER:

?ChgAreaCode("<Contacts>", "<Business Phone>")
				

where <Contacts> is a table name and <Business Phone> is a field containing the phone number.