Auto Formatting (Text v. Numerical)

[F1stman]

Hello All,

This one has really tried me.

I want to set a field, either in the table or on the forms where it appears,
to either allow regular text, or if the data follow the patter below,
automatically format to something like "@@-@@@-@@".

The formatting pattern will always be the same and the value to be formatted
will always be 24 numbers with no letters and no spaces. The formatting will
insert the hyphens.

I am quickly learning VBA so code is OK. Just please explain what action I
am using. Many thanks in advance. If this is impossible PLEASE let me know so
I can stop thinking about it.

Adam L. Kemp

 

[Marshall Barton]

I want to set a field, either in the table or on the forms where it appears,
to either allow regular text, or if the data follow the patter below,
automatically format to something like "@@-@@@-@@".

The formatting pattern will always be the same and the value to be formatted
will always be 24 numbers with no letters and no spaces. The formatting will
insert the hyphens.

That's not very clear. Some example data and how you want
or don't want it formatted would have helped.

You said that you could change the value is the table, but
you also said that the data would always be 24 digits. If
the value in the table field has been modified to include
dashes, then it won't be 24 digits anymore.

You can allow users to enter one string and display another
without affecting the original value, but this can be
confusing to untrained users that typically try to enter
what they see on the screen. If that caveat is of no
concern, then use two text boxes, the formatted one directly
on top of the data entry text box. The formatted text box
should (must on a continuous form) use an expression to
display the formatted value. For example, if the user
enters only 7 digits, then format it as above, else just
display what was entered, the expression would be like:

=IIf(boundtextbox Like "#######", Format(boundtextbox,
"##-###-##"), boundtextbox)

To permit data entry of the unformatted values in the bound
text box, use a line of code in the formatted text box's
GotFocus event procedure:
Me.boundtextbox.SetFocus