The information in this article applies to:
- Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.
Some states are adding new telephone area codes based on a phone number's
prefix. This article describes how to change a phone number's area code in
a table based on the prefix. This is accomplished by creating a table with
the prefix you want to find and the new area code. A Visual Basic function
then reads this table into an array and searches the phone number table and
changes the area code in the phone number table.
IMPORTANT: Before running this function, back up your database and make a
copy of your table.
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access versions 1.x and 2.0. For more information about Access Basic,
please refer to the "Introduction to Programming" manual in Microsoft
Access version 1.x or the "Building Applications" manual in Microsoft
Access version 2.0
MORE INFORMATION
To create the function, follow these steps:
- Create the following table that 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
- Type the phone number prefixes and area codes in the table Area Codes To
Change, for example:
Prefix Area Codes
----------------------
635 207
634 208
- Open a module or create a new one, and type the following code:
NOTE: In the following sample code, an underscore (_) is used as a
line-continuation character. Remove the underscore from the end of the
line when re-creating this code in Access Basic.
'*************************************************************
'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. This function operates on numbers
' with the following format "(206) 635-7050". The table name
' and field name containing the phone number must be passed
' as arguments.
'=============================================================
Function ChgAreaCode (tPhName, fldPhone)
Dim PhoneDB As Database
Dim tPhone As Recordset, tPrefix As 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 using this function, you pass (tPhName), the name of the table
containing 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 Debug window (or the Immediate window
in versions 1.x and 2.0), 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.
Keywords : kbprg PgmHowTo PgmObj
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowto
|