Project Perfect Project Management Blog

In a Microsoft Access database that is used for recording contact details, phone numbers are often stored in a string format with no spaces.  This enables checking if a number has been entered previously.  For example, a landline in Australia may have a two digit area code and an eight digit number.  If the area code is not used, the number is assumed to be in the calling area code.  If the number was 02 1234 5678 it may be stored as 0212345678 so it could be compared with numbers previously entered or analysis carried out to find all phone numbers starting with 02 which is the state of NSW.

Add to this the country code for some numbers (in Australia this is 61) and the fact that if you use the country code you drop the zero on the area code (61212345678) and you have a string that is difficult to dial correctly.  Complicating this is that there may be a number of phone numbers on the screen (business, personal, fax, mobile, direct).  How many people have never dialled a fax number when trying to contact a company?

One solution is to have a pop up screen and use a big font to display the number with logical spaces. I used Calibri 22 bold. The pop up appears if you double click the phone number.

To start, create a blank form. Only  the detail is visible.  I made it about 7cm wide by 3.6cm high.  Add a single text box called txtPhone.  It is 6.6cm wide and 1cm high.

On the original form in the phone number text box I put the following double click event

‘—————————————————————————————
‘ Procedure : txtPhoneNumber_DblClick
‘ Author : Neville Turbit
‘ Date : 18/11/2011
‘ Purpose : Open the pop up phone number
‘—————————————————————————————
‘
Private Sub txtPhoneNumber_DblClick(Cancel As Integer)
Dim strFormName As String

On Error GoTo Error_txtPhoneNumber_DblClick

strFormName = “frmPhoneNumberPopUp”
subOpenForms strFormName, Nz(Me.txtPhoneNumber, ” “)

Exit_txtPhoneNumber_DblClick:
On Error GoTo 0
Exit Sub

Error_txtPhoneNumber_DblClick:

MsgBox “An unexpected situation arose in your program.” & vbCrLf & _
“Please write down the following details:” & vbCrLf & vbCrLf & _
“Module Name: Form_frmContactsPhoneSub” & vbCrLf & _
“Type: VBA Document” & vbCrLf & _
“Calling Procedure: txtPhoneNumber_DblClick” & vbCrLf & _
“Error Number: ” & Err.Number & vbCrLf & _
“Error Description: ” & Err.Description

Resume Exit_txtPhoneNumber_DblClick
Resume

End Sub

You will notice I am using another function called subOpenForms. This is a function I use to open all forms as it has some error handling and better suits my needs. For some applications I incorporate security into the form opening. You can just use docmd.openform if you want.

‘—————————————————————————————
‘ Procedure : subOpenForms
‘ Author : Neville Turbit
‘ Date : 04/06/09
‘ Purpose : This function is used in the Click event of command buttons that opens forms
‘—————————————————————————————
‘
Public Sub subOpenForms(strFormName As String, Optional strLinkCriteria As String, Optional strQuery As String, Optional strWhere As String)

On Error GoTo Error_subOpenForms

‘————————————————————–
‘ Open specified form.
DoCmd.OpenForm strFormName, , strQuery, strWhere, , , strLinkCriteria

Exit_subOpenForms:
On Error GoTo 0
Exit Sub

Error_subOpenForms:

MsgBox “An unexpected situation arose in your program.” & vbCrLf & _
“Please write down the following details:” & vbCrLf & vbCrLf & _
“Module Name: modGeneric” & vbCrLf & _
“Type: Module” & vbCrLf & _
“Calling Procedure: subOpenForms” & vbCrLf & _
“Error Number: ” & Err.Number & vbCrLf & _
“Error Description: ” & Err.Description

Resume Exit_subOpenForms
Resume

End Sub

On the pop up form I use the following code in the On Open event.

‘—————————————————————————————
‘ Procedure : Form_Open
‘ Author : Neville Turbit
‘ Date : 18/11/2011
‘ Purpose : Display the phone number with spaces
‘—————————————————————————————
‘
Private Sub Form_Open(Cancel As Integer)
Dim strPhoneNo As String
Dim lenPhoneNo As Integer

On Error GoTo Error_Form_Open

strPhoneNo = Me.OpenArgs
lenPhoneNo = Len(strPhoneNo)

Select Case lenPhoneNo
Case Is = 8
Me.txtPhone = Left(strPhoneNo, 4) & ” ” & Right(strPhoneNo, 4)
Case Is = 10
If Left(strPhoneNo, 2) = “04″ Then
Me.txtPhone = Left(strPhoneNo, 4) & ” ” & Mid(strPhoneNo, 5, 3) & ” ” & Right(strPhoneNo, 3)
Else
Me.txtPhone = Left(strPhoneNo, 2) & ” ” & Mid(strPhoneNo, 3, 4) & ” ” & Right(strPhoneNo, 4)
End If
Case Else
Me.txtPhone = strPhoneNo
End Select

Exit_Form_Open:
On Error GoTo 0
Exit Sub

Error_Form_Open:

MsgBox “An unexpected situation arose in your program.” & vbCrLf & _
“Please write down the following details:” & vbCrLf & vbCrLf & _
“Module Name: Form_frmPhoneNumberPopUp” & vbCrLf & _
“Type: VBA Document” & vbCrLf & _
“Calling Procedure: Form_Open” & vbCrLf & _
“Error Number: ” & Err.Number & vbCrLf & _
“Error Description: ” & Err.Description

Resume Exit_Form_Open
Resume

End Sub

You will need to adjust this for your own phone number formats but it should not be too difficult. Basically you find the number of characters to see what sort of phone number you are dealing with and then reconstruct the phone number with spaces.

When I first introduced it to a company the response was incredible.  It was all people talked about when I asked them about the latest changes.  It made their life much easier.  In terms of return for investment, half an hours work got much more favourable response than days on other parts of the system.  Think about using it in your applications.