Formatting phone numbers in a continuous form 1

[uxgqy]

I have a continuous form that I use to remind me of clients I need to recontact. I have a module that checks the length and some of the content of each phone number. It then changes the formatting/input mask to suit the number in the record. The problem I'm having because it's a continuous form is each time it changes the formatting it changes every record. I thought I could fix this by adding an unbound text box that used the value of the bound field and then sets the formatting according to the code. Either I did it wrong or it's not the solution.

My questions are: Can it be done and if so how?

tia

David

 

[MajP]

It can not be done as you are describing. In a continous form there is really only one control and all others are just "paint on the screen". You change one, you change them all.

However, if it is not an editable field (for view only) base it on a custom function that returns a string

public function formatPhone(varPhoneNumber as variant) as string
select case len(varPhoneNumber)
case 7
formatPhaone = format(varPhoneNumber, "your format")
case 10
formatPhone = format(varPhoneNumber, "you 10 dig format")
case somethingelse
formatPhone =
case else
end select
end function

the control souce is then

=formatPhone([phoneNumberField])

 

[uxgqy]

Thanks. I'll try it out in the morning. The form only needs access to data entry forms so all the fields are just for show.

 

[PHV]

Have a look at conditional formatting.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

I might be wrong or I might have misread the original post, but I do not think you could do this in conditional formatting.
If I understand the post the original text is something like
5551234 or 7605551234 or 0117605551234 and then they would like to see something like.
555-1234
(760) 555-1234
011 (760) 555-1234

AKAIK you can change, Bold, Italic, Underline, Forecolor, BackColor, and Enabled.

 

[PHV]

OOps, sorry, my bad: misread the OP ...

 

[uxgqy]

majp

Thank you for your comments. Conditional formatting wouldn't work in this case. I have been trying to implement your suggestion all day and have not been able to get it to work. I can't seem to stop it applying any formatting (which is being sent as inputmask even though there is no entry by users) from applying to all records. It also only tries to apply the last style chosen. When I tried your coding all I got was the mask itself instead of the data formatted to the mask.

Any idea what I am doing wrong?

TIA

 

[MajP]

As I stated you can only get this to work as a way to view your records (not input and view). The reason is you can not put an input mask on the control and also have this function as the control source, I beleive the input mask overrides any format string.

I did it in a query and then used the query as my forms control source

My query:
SELECT emp2.phone, (formatPhone(nz([phone]))) AS newPhone
FROM emp2;

The input and output looks like this

phone newPhone
5551234 555-1234
7035552235 (703) 555-2235
0117035551234 011 (703) 555-1234

The function looks like this

Public Function formatPhone(varPhoneNumber As String) As String
  Select Case Len(varPhoneNumber)
     Case 7
       formatPhone = Format(varPhoneNumber, "000-0000")
     Case 10
        formatPhone = Format(varPhoneNumber, "(000) 000-0000")
     Case 13
         formatPhone = Format(varPhoneNumber, "000 (000) 000-0000")
     Case Else
   End Select

 

[MajP]

If you wanted to input a number without formatting, and apply different formatting on the same phone the only way I can think to do that is:
1. have the user input the number text (1235551234)
2. On the before update convert the string into a formatted
string (123) 555-1234
3. Save the string with the literal formatting. So you actually save (123) 555-1234 not 1235551234

 

[uxgqy]

majP

Thank you for all your help. It now works. The mask result was being caused by using InputMask formatting (9's and \, etc) instead of Formatting mask characters (0's).