Tidying a Field

Suppose you have some telephone fields (WorkPhone, HomePhone, CellPhone, Fax etc) and you need to add a prefix on occasion. Perhaps your telephone district has just split into two dialing areas, as has happened in Toronto a couple of times in the past fifteen years or so.

Currently many of your telephone numbers are stored as seven-digit values, but you need to prefix some of them with an area code "905-". Assuming that you don't have an automated means of doing this, you'll be reduced to performing the update by inspection.

We will build a command button to insert the string "905-" at the left-hand end of a telephone number.

Enter design mode for your form. Choose View, Toolbox and observe the small toolbar that appears with a variety of standard controls. Hover your mouse over each item on the toolbox toolbar to learn some names.

Choose the "Command Button" control by clicking on its image on the Toolbox toolbar, then click your mouse in a position quite close to your four telephone number fields. Don't worry if it gets in the way of later controls; we can always move it to suit our tastes.

Right-Click on the new control to raise the Properties box, and change its name to "cmd905" and the caption to be "905" (assuming, like us, you wish to augment some telephone numbers with the 905-area-code).

Still in the Properties box, locate the "On Click" event; choose the triple-period button and elect for the Code Builder.

You will be launched into the VBE with a small procedure that looks just like this:

Private Sub cmd905_Click()
End Sub

Type the code shown below to complete the procedure:

Private Sub cmd905_Click()
Dim ctlCurrentControl As Control
Set ctlCurrentControl = Screen.PreviousControl
Dim strPhone As String
strPhone = ctlCurrentControl.OldValue
strPhone = "905-" & strPhone
ctlCurrentControl = strPhone
End Sub

Close your design window.

Back in your user form you'll see your new command button, with a caption "905". Select one of your telephone number fields containing data, then click on your "905" button. You should see the string "905-" appear in front of the previous contents of the field. Don't click "905" again, or you'll end up with two prefixes.

If you accidentally clicked in a field that was empty, that contained no telephone number, you probably received a warning message along the lines of "Run time error 94: Invalid use of Null". We will address that problem right now.

Private Sub cmd905_Click()
Dim ctlCurrentControl As Control
Set ctlCurrentControl = Screen.PreviousControl
If IsNull(ctlCurrentControl) Then
MsgBox "The selected field was null!"
Else
Dim strPhone As String
strPhone = ctlCurrentControl.OldValue
strPhone = "905-" & strPhone
ctlCurrentControl = strPhone
End If
End Sub

We have inserted a statement that suggests that if the field contents are null, we are to display a short message of our own in a pop-up box, otherwise we are to modify the contents of the field as before.

Search