Format vs. Input Mask and Force First Letter to be Capitalized

[CAD Fiend]

Hello,

Q #1
For some reason, I am getting confused about the difference between
Input Mask and Format. Can anyone tell me the differnce between the two
and when/when not to use them?

Q #2
Also, I have a combo box (cmbFirstName) that writes to a table
(tblFirstName) and I'd like to have it so that the if user forgets to
type the first letter as a capital letter, it (cmbFirstName) will do it
for them. I tried to use this below (which I found on one of John
Vinson's replies from last week) and it worked fine in the form, but
when I looked in the table to check if it passed it correctly, the entry
was still ALL lowercase. What do I need or what am I missing?

Private Sub cmbFirstName_AfterUpdate()
' Only convert fields that are not already mixed case
' This will leave correct but not "proper" names such as
' MacDonald, van der Steen, and de Vargas undamaged
If StrComp(Me!cmbFirstName, LCase(Me!cmbFirstName), 0) = 0 Then
Me!cmbFirstName= StrConv(Me!cmbFirstName, vbProperCase)
End If

End Sub

TIA.

Phil.

 

[Tony Vrolyk]

Q1 Format indicates how the data is displayed. Format of a table field will
flow to forms and reports though you can change it. Input mask helps the
user input data by not requiring they enter certain common characters - i.e.
the hyphen in Social Security number of the parenthesis in phone number.

Q2 How about this. Aircode should be checked for errors, watch for line
wrap.

Private Sub cmbFirstName_AfterUpdate()
If Not IsNull(Me.cmbFirstName) Then
Me.cmbFirstName = UCase(Left(Me.cmbFirstName,1)) &
Right(Me.cmbFirstName,len(Me.cmbFirstName)-1)
End If
End Sub

It doesn't check if the first letter is already upper case but who cares if
you upper case an upper case letter?

Tony

 

[CAD Fiend]

Tony,

Thanks for your reply. Please see my in-line comments.

Phil.

Q1 Format indicates how the data is displayed. Format of a table field will

So if I want the table to display properly you're saying to format the "table"
field only? I thought I had to only format the form field, but maybe you're
saying that I have to do both the table and form, right?

flow to forms and reports though you can change it. Input mask helps the
user input data by not requiring they enter certain common characters - i.e.
the hyphen in Social Security number of the parenthesis in phone number.

Q2 How about this. Aircode should be checked for errors, watch for line
wrap.

BTW, I was GOING to ask that VERY question, about the line wrap. I don't know
HOW to do that. Can you please provide me with an EXACT example, so I can make a
note of it for the future?

Private Sub cmbFirstName_AfterUpdate()
If Not IsNull(Me.cmbFirstName) Then
Me.cmbFirstName = UCase(Left(Me.cmbFirstName,1)) &

Because, when I place THIS code AS IS, in my event procedure it shows the red
text, indicating (I guess) that it is incorrectly syntaxed.

Right(Me.cmbFirstName,len(Me.cmbFirstName)-1)
End If
End Sub

It doesn't check if the first letter is already upper case but who cares if
you upper case an upper case letter?

Yeah, I don't care either.

 

[Tony Vrolyk]

My comments are inline as well

So if I want the table to display properly you're saying to format the
"table"
field only? I thought I had to only format the form field, but maybe
you're
saying that I have to do both the table and form, right?

Setting the format on the field in the table is optional. If you do, anytime
you add that field to a form, query or report it will pick up that format.
But even then you can change it if you want. Let say you want a date field
and in the table you set it to Short Date (m/d/yyyy). When you add a control
to a form that is bound to that field it will also be formatted as a Short
Date. You can change the format on the form to something like mm/dd/yy or
mm/yyy or whatever if you want. Then on the form it will display with one
format and in the table with another.

In most cases I do not set a format on the table but rather in the form or
report. I prefer to see the data in the table in its 'purest form'. Then by
using format and input masks I present the data to the user in a user
friendly way.

My users never view data in the table directly. Usually in forms and reports
and sometimes in queries. In each case I can control how the data is
displayed.

Also I NEVER set input masks in the table only on the form or report. Can't
remember all the details but it just caused too many problems. When I do use
an Input Mask on a form I set it to store the pure data without the mask
When creating a mask with the Input Mask wizard it will as you at the end do
you want to store with the input mask characters or not. I always choose
not.
So a phone number is stored 8155551234 even though the input mask shows it
as (815) 555-1234 and SSNs are stored as 123456789 even though they are
shown as 123-45-6789.

BTW, I was GOING to ask that VERY question, about the line wrap. I don't
know
HOW to do that. Can you please provide me with an EXACT example, so I can
make a
note of it for the future?

Most newsreaders submit messages in pure text format which has a limit to
the
number of characters wide it will show so it wraps the line if it is too
long. If I add line numbers below here is what you get.

1 Private Sub cmbFirstName_AfterUpdate()
2 If Not IsNull(Me.cmbFirstName) Then
3 Me.cmbFirstName = UCase(Left(Me.cmbFirstName,1)) &
4 Right(Me.cmbFirstName,len(Me.cmbFirstName)-1)
5 End If
6 End Sub

If you paste in the code ( without the line numbers ) just go to the end of
line 3 and hit Delete. That will bring line 4 back up in line 3 as it is
supposed to be. Technically the code can be written to wrap but that
requires
an underscore character which I didn't type into the sample. If you
copy/paste the following code you should not have to worry
about the line wrap.

Private Sub cmbFirstName_AfterUpdate()
If Not IsNull(Me.cmbFirstName) Then
Me.cmbFirstName = UCase(Left(Me.cmbFirstName, 1)) & _
Right(Me.cmbFirstName, Len(Me.cmbFirstName) - 1)
End If
End Sub

Notice the extra underscore? That tells Access that this line of code is
continued on the next line because of course a line of code can't end in an
ampersand.

I really wish we would all get out of the dark ages and use at least Rich
Text so we could not worry about line wrap and (GOD FORBID!) use bold or
underline. But us techy people get very stuck in our ways and we like text
edirors and command line interfaces - ugh!

Hope that clears it up.
Tony

 

[CAD Fiend]

Thanks for your help, Tony!

My comments are inline as well



Setting the format on the field in the table is optional. If you do, anytime
you add that field to a form, query or report it will pick up that format.
But even then you can change it if you want. Let say you want a date field
and in the table you set it to Short Date (m/d/yyyy). When you add a control
to a form that is bound to that field it will also be formatted as a Short
Date. You can change the format on the form to something like mm/dd/yy or
mm/yyy or whatever if you want. Then on the form it will display with one
format and in the table with another.

In most cases I do not set a format on the table but rather in the form or
report. I prefer to see the data in the table in its 'purest form'. Then by
using format and input masks I present the data to the user in a user
friendly way.

My users never view data in the table directly. Usually in forms and reports
and sometimes in queries. In each case I can control how the data is
displayed.

Also I NEVER set input masks in the table only on the form or report. Can't
remember all the details but it just caused too many problems. When I do use
an Input Mask on a form I set it to store the pure data without the mask
When creating a mask with the Input Mask wizard it will as you at the end do
you want to store with the input mask characters or not. I always choose
not.
So a phone number is stored 8155551234 even though the input mask shows it
as (815) 555-1234 and SSNs are stored as 123456789 even though they are
shown as 123-45-6789.


Most newsreaders submit messages in pure text format which has a limit to
the
number of characters wide it will show so it wraps the line if it is too
long. If I add line numbers below here is what you get.

1 Private Sub cmbFirstName_AfterUpdate()
2 If Not IsNull(Me.cmbFirstName) Then
3 Me.cmbFirstName = UCase(Left(Me.cmbFirstName,1)) &
4 Right(Me.cmbFirstName,len(Me.cmbFirstName)-1)
5 End If
6 End Sub

If you paste in the code ( without the line numbers ) just go to the end of
line 3 and hit Delete. That will bring line 4 back up in line 3 as it is
supposed to be. Technically the code can be written to wrap but that
requires
an underscore character which I didn't type into the sample. If you
copy/paste the following code you should not have to worry
about the line wrap.

Private Sub cmbFirstName_AfterUpdate()
If Not IsNull(Me.cmbFirstName) Then
Me.cmbFirstName = UCase(Left(Me.cmbFirstName, 1)) & _
Right(Me.cmbFirstName, Len(Me.cmbFirstName) - 1)
End If
End Sub

Notice the extra underscore? That tells Access that this line of code is
continued on the next line because of course a line of code can't end in an
ampersand.

I really wish we would all get out of the dark ages and use at least Rich
Text so we could not worry about line wrap and (GOD FORBID!) use bold or
underline. But us techy people get very stuck in our ways and we like text
edirors and command line interfaces - ugh!

Hope that clears it up.
Tony