Need multiple Phone Masks for same field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a contact list with phone number fields. The contacts could be from a
number of countries each with a different phone number mask.

The contact table has a "Country" field which is a lookup from a Country
Table.
I was thinking about adding a Phone Number mask field to the Country Table
and somehow use that to format the phone numbers.

So, is it possible on my contact form, that after I select the country for
the contact, the form knows to lookup the mask from the country table and it
apply it?

This is a continous form with all the fields in the "detail" section.

The other question is does this apply a potentially different mask to all
visible phone numbers? ie 1st row shows US mask, 2nd row shows Germany
mask...etc...

Thanks,
 
I put the following code in to try and get it to work for the most common
countries.
I follow the variables through in VBA step by Step and the value for fName
changes from "2482763777" to "(248) 276-3777" when I hover the mouse over the
variable but it never udpates the field in the form.... it still stays with
just the number.
Any Ideas what could prevent the update? In addition to how I can get rid
of the select case and just have it lookup the mask in the country table?


Private Sub BuyerOffice_AfterUpdate()
Select Case Me!Country
Case "Mexico"
Call SetFormat(Me!BuyerOffice, "@@-@@@-@@@-@@@")
Case "USA"
Call SetFormat(Me!BuyerOffice, "(@@@) @@@-@@@@")
End Select
End Sub

Sub SetFormat(fName, strFormat)
If IsNull(fName) = False Then
fName = Format(fName, strFormat)
End If
End Sub
 
Hi, Mike.

Yes, this is a good approach. Include the Input Mask as the 3rd column in
your Country combo box. Set its Column Width, along with the primary key
column to 0" to just show the country name.

A textbox, however, can only have a single input mask at a time, so that
when you change the input mask, other records showing in a continuous form
will lose their displayed formatting (although they're value will remain the
same).

The solution is to create a dialog form in which to enter the phone number,
apply the input mask to this control, and copy it to your input form control
in its AfterUpdate event.

Main Form Country Code Combo Box AfterUpdate event:

Private Sub cboCountryCode_AfterUpdate()
On Error Resume Next

Dim strInputMask As String

' Get input mask for selected country
strInputMask = Me!cboCountryCode.Column(2)

' Open dialog form
DoCmd.OpenForm _
FormName:="Input Phone Number", _
View:=acNormal, _
WindowMode:=acDialog

' Control is passed to the dialog form here

' Move the cursor to the next control
Me!txtNextField.SetFocus

End Sub

Dialog Form Open Event:

Private Sub Form_Open(Cancel As Integer)
' Set input mask from third column of main form country code combo box:
Me![txtPhoneNumber].InputMask =
Forms![PhoneNumbers]!cboCountryCode].Column(2)
End Sub

Dialog Form Textbox Control AfterUpdate Event:

Private Sub txtPhoneNumber_AfterUpdate()
Forms![PhoneNumbers]![txtPhoneNumber] = Me!txtPhoneNumber
DoCmd.Close
End Sub

Be sure also that all of your input masks store their formatting characters.
Probably the easiest way to ensure this is to use your InputMask wizard in
form design view, create the input mask, select "Store characters", and cut
and paste the generated mask to your CountryCode table.

Hope that helps.
Sprinks
 
On Mon, 24 Jan 2005 07:45:05 -0800, MikeZz

don't spend to much time on this, because it will never work for
international telephone numbers.

the country code can be 2 or 3 digits (leaving the 00 out)
the area code can be 1 to 3 digits
(and other exeptions)

example:
Vienna (austria) +43 (1) xxxxxxx
Eisenerz: +43 (50) xxxxx
Innsbruck: +43 (512) xxxxx

try this 3 for a single country and you will quit
 
Hi, Mike.

In light of the complexities Andy brings up, it might still be worth it.
You'd have to filter a second combo box, however, by the choice of Country,
to narrow it to the types of codes therein. This would necessitate detail
table of Country, and the input masks would be stored there.

Sprinks
 
Thanks for the help, maybe there is a simpler way....

I don't deal with TO many countries, so I don't think the Multiple formats
per country would be that big of an issue if I just have a multiple Country
Names for the same country...
ie CountryNames = Austria-Viena; Austria-Eisenerz; etc...

So, if I use that idea, could I add the following to the
BuyerOffice_AfterUpdate (which is the field for their office phone number):
Look up which country is already selected in the country code and then apply
the mask to the phone number I just entered in BuyerOffice

Private Sub cboBuyerOffice_AfterUpdate()
On Error Resume Next
Dim strInputMask As String
strInputMask = Me!cboCountryCode.Column(2)
Me!BuyerOffice = Format(Me!BuyerOffice, strInputMask)
End Sub

Thanks,
MikeZz
 
Hi, Mike.

I'm confused. An Input Mask is normally used to assist the user in
formatting the input as he types, and to ensure that he completes the field.
It has no value that I can see once the data is already inputted.

Moreover, an Input Mask applies to the control--you cannot have a different
one for each record. That is why I suggested the Dialog form method, which
is also used when you're filtering one combo box by a previous one on a
continuous form. Since the masks I suggested were saving the formatting
characters to the unbound control on the dialog form, these characters were
saved to your main form control intact.

But, if you're not looking to assist the user during data entry and merely
wish to format the entry for subsequent display when editing and viewing the
record, my only other suggestion would be to create a custom function, such
that you convert the number-only input to the formatted input. Seems like
this would need to be "hard-wired", and would require error-checking to make
sure the number of digits entered matched the number required for that mask.

Something like, in the BeforeUpdate event of the Buyer-Office control:

Dim strPh as String ' Working string as entered into BuyerOffice
Dim blnInvalid As Boolean ' Set to true if # of characters is incorrect

strPh = Me![BuyerOffice]
blnInvalid = False

Select Case Me![CountryName]
Case 1 ' The code for a country with input mask (999) 000-0000
If len(strPh) <> 10 Then
blnInvalid = True
Else
Me![BuyerOffice] = "(" & left(strPh,3) & ")" & Mid (strPh,4,3) & "-" &
Right(strPh,4)
End If
' Other cases
End Select

If blnInvalid = True Then
Cancel = True
MsgBox "Wrong number of digits for this country. Please reenter."
End If

But, although the former solution requires three different procedures, it is
quite clean, and feels totally intuitive. Your choice.

Hope that helps.
Sprinks
 
I got it to work pretty much as planned.
It formats it correctly after I update it and saves the text formatting...
And the code is simpler than I expected.
Now if I can only get it to drop the () if I don't put in an area code....
but hey, it works great and that's all I can ask for!

Thanks!

Here's what the final code looks like:
Private Sub BuyerCell_AfterUpdate()
On Error Resume Next
Dim strInputMask As String
strInputMask = Me!cboCountry.Column(1)
Me!BuyerCell = Format(Me!BuyerCell, strInputMask)
End Sub


Sprinks said:
Hi, Mike.

I'm confused. An Input Mask is normally used to assist the user in
formatting the input as he types, and to ensure that he completes the field.
It has no value that I can see once the data is already inputted.

Moreover, an Input Mask applies to the control--you cannot have a different
one for each record. That is why I suggested the Dialog form method, which
is also used when you're filtering one combo box by a previous one on a
continuous form. Since the masks I suggested were saving the formatting
characters to the unbound control on the dialog form, these characters were
saved to your main form control intact.

But, if you're not looking to assist the user during data entry and merely
wish to format the entry for subsequent display when editing and viewing the
record, my only other suggestion would be to create a custom function, such
that you convert the number-only input to the formatted input. Seems like
this would need to be "hard-wired", and would require error-checking to make
sure the number of digits entered matched the number required for that mask.

Something like, in the BeforeUpdate event of the Buyer-Office control:

Dim strPh as String ' Working string as entered into BuyerOffice
Dim blnInvalid As Boolean ' Set to true if # of characters is incorrect

strPh = Me![BuyerOffice]
blnInvalid = False

Select Case Me![CountryName]
Case 1 ' The code for a country with input mask (999) 000-0000
If len(strPh) <> 10 Then
blnInvalid = True
Else
Me![BuyerOffice] = "(" & left(strPh,3) & ")" & Mid (strPh,4,3) & "-" &
Right(strPh,4)
End If
' Other cases
End Select

If blnInvalid = True Then
Cancel = True
MsgBox "Wrong number of digits for this country. Please reenter."
End If

But, although the former solution requires three different procedures, it is
quite clean, and feels totally intuitive. Your choice.

Hope that helps.
Sprinks

MikeZz said:
Thanks for the help, maybe there is a simpler way....

I don't deal with TO many countries, so I don't think the Multiple formats
per country would be that big of an issue if I just have a multiple Country
Names for the same country...
ie CountryNames = Austria-Viena; Austria-Eisenerz; etc...

So, if I use that idea, could I add the following to the
BuyerOffice_AfterUpdate (which is the field for their office phone number):
Look up which country is already selected in the country code and then apply
the mask to the phone number I just entered in BuyerOffice

Private Sub cboBuyerOffice_AfterUpdate()
On Error Resume Next
Dim strInputMask As String
strInputMask = Me!cboCountryCode.Column(2)
Me!BuyerOffice = Format(Me!BuyerOffice, strInputMask)
End Sub

Thanks,
MikeZz
Click to expand...
Click to expand...
 
Hey, Mike.

Glad it's working for you.

Sprinks

MikeZz said:
I got it to work pretty much as planned.
It formats it correctly after I update it and saves the text formatting...
And the code is simpler than I expected.
Now if I can only get it to drop the () if I don't put in an area code....
but hey, it works great and that's all I can ask for!

Thanks!

Here's what the final code looks like:
Private Sub BuyerCell_AfterUpdate()
On Error Resume Next
Dim strInputMask As String
strInputMask = Me!cboCountry.Column(1)
Me!BuyerCell = Format(Me!BuyerCell, strInputMask)
End Sub


Sprinks said:
Hi, Mike.

I'm confused. An Input Mask is normally used to assist the user in
formatting the input as he types, and to ensure that he completes the field.
It has no value that I can see once the data is already inputted.

Moreover, an Input Mask applies to the control--you cannot have a different
one for each record. That is why I suggested the Dialog form method, which
is also used when you're filtering one combo box by a previous one on a
continuous form. Since the masks I suggested were saving the formatting
characters to the unbound control on the dialog form, these characters were
saved to your main form control intact.

But, if you're not looking to assist the user during data entry and merely
wish to format the entry for subsequent display when editing and viewing the
record, my only other suggestion would be to create a custom function, such
that you convert the number-only input to the formatted input. Seems like
this would need to be "hard-wired", and would require error-checking to make
sure the number of digits entered matched the number required for that mask.

Something like, in the BeforeUpdate event of the Buyer-Office control:

Dim strPh as String ' Working string as entered into BuyerOffice
Dim blnInvalid As Boolean ' Set to true if # of characters is incorrect

strPh = Me![BuyerOffice]
blnInvalid = False

Select Case Me![CountryName]
Case 1 ' The code for a country with input mask (999) 000-0000
If len(strPh) <> 10 Then
blnInvalid = True
Else
Me![BuyerOffice] = "(" & left(strPh,3) & ")" & Mid (strPh,4,3) & "-" &
Right(strPh,4)
End If
' Other cases
End Select

If blnInvalid = True Then
Cancel = True
MsgBox "Wrong number of digits for this country. Please reenter."
End If

But, although the former solution requires three different procedures, it is
quite clean, and feels totally intuitive. Your choice.

Hope that helps.
Sprinks

MikeZz said:
Thanks for the help, maybe there is a simpler way....

I don't deal with TO many countries, so I don't think the Multiple formats
per country would be that big of an issue if I just have a multiple Country
Names for the same country...
ie CountryNames = Austria-Viena; Austria-Eisenerz; etc...

So, if I use that idea, could I add the following to the
BuyerOffice_AfterUpdate (which is the field for their office phone number):
Look up which country is already selected in the country code and then apply
the mask to the phone number I just entered in BuyerOffice

Private Sub cboBuyerOffice_AfterUpdate()
On Error Resume Next
Dim strInputMask As String
strInputMask = Me!cboCountryCode.Column(2)
Me!BuyerOffice = Format(Me!BuyerOffice, strInputMask)
End Sub

Thanks,
MikeZz




:

Hi, Mike.

In light of the complexities Andy brings up, it might still be worth it.
You'd have to filter a second combo box, however, by the choice of Country,
to narrow it to the types of codes therein. This would necessitate detail
table of Country, and the input masks would be stored there.

Sprinks

:

I have a contact list with phone number fields. The contacts could be from a
number of countries each with a different phone number mask.

The contact table has a "Country" field which is a lookup from a Country
Table.
I was thinking about adding a Phone Number mask field to the Country Table
and somehow use that to format the phone numbers.

So, is it possible on my contact form, that after I select the country for
the contact, the form knows to lookup the mask from the country table and it
apply it?

This is a continous form with all the fields in the "detail" section.

The other question is does this apply a potentially different mask to all
visible phone numbers? ie 1st row shows US mask, 2nd row shows Germany
mask...etc...

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top