Phone Number Validation

[scouser]

I have a PhoneNumber field on a form that is a required field. However this field is text so a user can enter a number mixed with text A BIG NO NO!! How can I prevent this. (UK Phone Numbers only, number lenghts vary!!). I presume this to be an AfterUpdate event procedure on the PhoneNumber field. Can any of you clever coders out here help me ??

I have searched forum for an answer but couldn't see one, so apologise if this has been posted previously.
Cheers,
Phil.

 

[dcx693]

I'm not the best at this, but I know this problem can be solved using input masks. You can restrict the entries to numbers, and you can have variable length number entries. Look up the topic in the Access online help.

 

[Mile-O]

I'm not the best at this, but I know this problem can be solved using input masks.

The problem with UK numbers is they follow one of two patterns and both are common.

i.e.

0000 000 0000 and 00000 000 000 (same amount of characters but area codes are 4 or 5 characters.

I presume this to be an AfterUpdate event procedure on the PhoneNumber field.

No. It's a BeforeUpdate event. AfterUpdate saves it into the field; before update allows you to validate it and cancel if the criteria is not met.

So, that being said - put this in a module:

Public Function NumbersOnly(ByVal strText As String) As Boolean
    Dim intCounter As Integer
    For intCounter = 1 To Len(strText)
        If Not IsNumeric(Mid(strText, intCounter, 1)) Then
            NumbersOnly = False
            Exit Function
        End If
    Next intCounter
    NumbersOnly = True
End Function

In the BeforeUpdate event.

If Not NumbersOnly(Me.MyTextbox) Then 
    MsgBox "Please enter a proper phone number", vbExclamation
    Cancel = True
End If

As for splitting the numbers apart depending on their area code, then it's just not possible due to codes like 0208, 0141, etc.

 

[dcx693]

The problem with UK numbers is they follow one of two patterns and both are common.

Details...details...I didn't not consider that.

How about an input mask like this:
00009-000-0009
It limits the inputs to digits 0-9, though it does allow you to enter 12 characters. You can use some error checking to alert the user.

If you just want to limit the input characters to digits 0-9, and to limit it to 11 characters, how about this for the input mask:
00000000000

 

[scouser]

Thanks

Thanks Mile-O, I will give that a go tonight.
Cheers,
Phil.

 

[scouser]

Thanks

Thank you dcx693. I will have a play about this evening.
Thanks for your time.
Phil.

 

[scouser]

Nearly There

Guys, both angles work. One thing. I want a space between the area code and number. I feel the input mask will not allow blanks? Mile-O can your code be modified to allow a space, but still throw an error for A-Z.
Cheers,
Phil.

 

[Mile-O]

Re: Nearly There

Guys, both angles work. One thing. I want a space between the area code and number. I feel the input mask will not allow blanks?

But how will you know where the area code ends and the number starts? Four digits? Five digits?

Now, why not have two fields? AreaCode and PhoneNumber.

On the form, disable the PhoneNumber field until the AreaCode is entered. Depending on the area code entered then you can set the Input Mask for the Phone Number.

 

[Rich]

The Area Code should be in a separate look up table anyway

 

[Mile-O]

The Area Code should be in a separate look up table anyway

Well, AreaCodeID and PhoneNumber.

 

[scouser]

Good Points

Well a bit more work needs to be done!!
So a seperate table for area codes.

Two columns:
CodeID AutoNumber
AreaCode Text

Insert new field in tblCustomer (AreaCode) as Number - Long Integer

Relationship between tables 1-M
(One AreaCodeID to many areaCodes).

How would users enter areaCode into the field as if it was a combo it would be limited to a list?

My customer table is the only table that is not created from a query.

Well guys when the basics are complete how would I disable the PhoneNumber Field until the Area code is entered + set the number length according to the length of the area code?
Would it be BeforeUpdate event;
If area code = ??????
Then
PhoneNumber = ?????
Else IF
area code = ???? Then
PhoneNumber = ??????

then somehow enable the PhoneNumber field?????


Sorry for my complete ignorance guys. I have improved my knowledge, just going to take time.
Thanks for your efforts,
Phil.

 

[Rich]

It's a one to many relationship between Area Codes and other tables, one area can have many numbers