?
Solved

Input Mask, only letters all capital

Posted on 2009-05-05
12
Medium Priority
?
3,971 Views
Last Modified: 2013-12-20
Hi Guys

I need to validate data entry in a form, let's said fieldNAme
I do not like Input Mask that much, >L????????, because it put those _ and if the user starts typing in the middle it will capture the blank spaces and the begining
Now i am using and After update event to transform the input to upper cases,
Let Me.LastName.Value = VBA.Strings.UCase(VBA.Strings.Trim(Me.LastName.Value))

 but i would like to restrict it only to letters, i do not want the user to be able to enter numbers or symbols

PLease comment

thanks
0
Comment
Question by:titorober23
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 7

Expert Comment

by:shambalad
ID: 24306191
I just tried to post this, but it doesn't appear to have updated; so I'm trying it again.
Following sub returns only capped letters.
Todd

Sub CapsFilter(KeyAscii As Integer)
' convert letters to uppercase
' called FROM Textbox_KeyPress event procedure
        KeyAscii = Asc(UCase(Chr(KeyAscii)))
        If KeyAscii < Asc("A") Or KeyAscii > Asc("Z") Then KeyAscii = 0
End Sub

Open in new window

0
 
LVL 7

Expert Comment

by:shambalad
ID: 24306224
FWIW -
Although this next sub is not exactly what you requested, it follows along the same lines.

Sub ExpandedCapsFilter(KeyAscii As Integer)
' ExpandedCapsFilter
' KeyPress filter for textbox controls:
' accepts backspace (8),space (32),- (45),period (46),& (38),' (39)* (42),digits 0-9 (48-57)
' convert letters to uppercase
' called FROM Textbox_KeyPress event procedure
    If ((KeyAscii = 8) Or (KeyAscii = 38) Or (KeyAscii = 42) Or _
        (KeyAscii = 32) Or (KeyAscii = 44) Or (KeyAscii = 46) Or _
        (KeyAscii = 48) Or (KeyAscii = 49) Or (KeyAscii = 50) Or _
        (KeyAscii = 51) Or (KeyAscii = 52) Or (KeyAscii = 53) Or _
        (KeyAscii = 54) Or (KeyAscii = 55) Or (KeyAscii = 56) Or _
        (KeyAscii = 57) Or (KeyAscii = 45) Or (KeyAscii = 39)) Then
        Exit Sub
    Else
        KeyAscii = Asc(UCase(Chr(KeyAscii)))
        If KeyAscii < Asc("A") Or KeyAscii > Asc("Z") Then KeyAscii = 0
    End If
End Sub

Open in new window

0
 

Accepted Solution

by:
titorober23 earned 0 total points
ID: 24306274
I am using this

Private Sub LastName_AfterUpdate()
Let Me.LastName.Value = VBA.Strings.UCase(VBA.Strings.Trim(Me.LastName.Value))
End Sub

Private Sub LastName_KeyDown(KeyCode As Integer, Shift As Integer)
If (KeyCode >= 65 And KeyCode <= 122) Or KeyCode = 8 Or KeyCode = 9 Or KeyCode = 27 Or KeyCode = 13 Then 'only allow A-Z and Backspace

Else
    DoCmd.CancelEvent
End If
End Sub

Do you think this will slow down my system
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:shambalad
ID: 24308945
I don't think that your method will slow things down that much and essentially, it will work. For that matter, there's nothing in this discussion so far, or in what I lay out in this current post that is going to have a noticeable time lag to it.
Anyway, here are some observations:
Probably the two biggest differences between your approach and my suggested approach are using the KeyDown event instead of the KeyPress event, and setting the case after the whole string has been entered.
The KeyPress and KeyDown events would appear to be almost identical, but there are some subtle differences. Note that the variable 'KeyCode' is passed to the KeyDown event and the variable 'KeyAscii' is passed to the KeyPress event. For instance, while the textbox has focus, if you press the shift key, a KeyDown event is fired, but a KeyPress event is not. When you hold the shift key down for more than instant, multiple KeyDown events are triggered. Personally, I feel that the multiple firings of the KeyDown event makes a strong argument for using the KeyPress event. Note, too, that the KeyCode parameter passed to the KeyPress event subroutine is '65' regardless of whether an 'A' or an 'a' was pressed (the second parameter 'Shift', is passed with a zero ('0') for the 'a' and a one ('1') for the 'A'. In contrast, the KeyAscii parameter passed to the KeyPress event is '65' for 'A' and '97' for 'a'. Filtering on the KeyDown event means you also have to take account of the TAB, or HOME, END and ENTER keys on the keyboard (which, by the way, are inactivated by your current filter on the KeyDown event). None of those keys (i.e. TAB, HOME, END and ENTER) fire a KeyPress event.
The other difference is changing the case of the string after it has been entered with the 'AfterUpdate' event. A key difference in setting the case on the KeyPress event is that the user 'sees' each character being entered as a capital letter, rather than seeing the whole string change to caps after exiting the text box. Again, this is a matter of preferences, but I think it is better for the user to see the letters appear as caps as they are being entered.
Assuming you were to change the letters to caps as they are being entered, it would seem that the code for the 'AfterUpdate' would be redundant. To cover all circumstances, however, it would be better to have a routine there too to cover those instances when someone pastes a string into the textbox. Note that if the pasted string contained characters other than letters, they wouldn't be caught by the UCase function you are using in the AfterUpdate event sub.
I would suggest using another subroutine (ConvertStringToCaps) which I have attached below.
When working with subroutines that may be used in more than one place, it is generally a good practice to make the routine independent (i.e. create a separate subroutine for that specific process), then make a call to that subroutine from wherever that process is needed.
As concerns using 'DoCmd.CancelEvent': Your usage in the KeyDown event sub is not an error. Nonetheless, you should use care with the CancelEvent method, inasmuch as you can't use it universally in VBA with events. For instance, you will get an error if you try using 'DoCmd.CancelEvent' in a subroutine for a KeyPress or MouseDown (right-click) event.


Private Sub LastName_AfterUpdate()
   LastName = ConvertStringToCaps(Nz(LastName, ""))
End Sub
 
Private Sub LastName_KeyPress(KeyAscii As Integer)
   NameCapsFilter KeyAscii
End Sub
 
Sub NameCapsFilter(KeyAscii As Integer)
' convert letters to uppercase
' Allows backspace (8) and space (32)
' called FROM Textbox_KeyPress event procedure
 
   If ((KeyAscii = 8) Or (KeyAscii = 32)) Then Exit Sub
   KeyAscii = Asc(UCase(Chr(KeyAscii)))
   If KeyAscii < Asc("A") Or KeyAscii > Asc("Z") Then KeyAscii = 0
End Sub
 
Private Function ConvertStringToCaps(strInput) As String
   Dim strCharacter As String
   Dim intCounter As Integer
   Dim intLength As Integer
   Dim strResult As String
   Dim intAscii As Integer
   
   ' Converts input string to caps
   ' Returns capital letters, along with spaces
   ' that are neither leadding or trailing.
   ' All other characters are dropped.
   
   If Not strInput = "" Then
      ' Trim leading and triling spaces
      strInput = Trim(strInput)
      ' get length of string
      intLength = Len(strInput)
      ' Evaluate each character in string.
      ' If it is valid, append to result
      Do While intCounter < intLength
         intCounter = intCounter + 1
         strCharacter = Mid$(strInput, incounter, 1)
         intAscii = Asc(strCharacter)
         ' Allow spaces within the name
         If KeyAscii = 32 Then
            strResult = strResult & strCharacter
         Else
            ' Convert to upper case
            intAscii = Asc(UCase(Chr(intAscii)))
            ' If capital letter, add to name
            If Not intAscii < Asc("A") Or intAscii > Asc("Z") Then
               strResult = strResult & strCharacter
            End If
         End If
      Wend
   End If
   ConvertStringToCaps = strResult
End Function

Open in new window

0
 
LVL 7

Assisted Solution

by:shambalad
shambalad earned 400 total points
ID: 24309054
Correction on the ConvertStringToCaps subroutine

Private Function ConvertStringToCaps(strInput As String) As String
   Dim strCharacter As String
   Dim intCounter As Integer
   Dim intLength As Integer
   Dim strResult As String
   Dim intAscii As Integer
   
   ' Converts input string to caps
   ' Returns capital letters, along with spaces
   ' that are neither leadding or trailing.
   ' All other characters are dropped.
   
   If Not strInput = "" Then
      ' Trim leading and trailing spaces
      strInput = Trim(strInput)
      ' get length of string
      intLength = Len(strInput)
      ' Evaluate each character in string.
      ' If it is valid, append to result
      While intCounter < intLength
         intCounter = intCounter + 1
         strCharacter = Mid$(strInput, intCounter, 1)
         intAscii = Asc(strCharacter)
         ' Allow spaces within the name
         If intAscii = 32 Then
            strResult = strResult & strCharacter
         Else
            ' Convert to upper case
            intAscii = Asc(UCase(Chr(intAscii)))
            ' If capital letter, add to name
            If Not (intAscii < Asc("A") Or intAscii > Asc("Z")) Then
               strResult = strResult & Chr(intAscii)
            End If
         End If
      Wend
   End If
   ConvertStringToCaps = strResult
End Function

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24312245
titorober23,

All this code looks way too complicated fo my tastes.
:-O

If I were you, I would keep this simple and use seperate textboxes for each character.
Mask each with >L
(Investigate using the autotab property)

Then concatenate them together for display purposes.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24358918
Then for the record, can you state what your solution was?

My post suggested a "simplier" solution as well.
0
 
LVL 7

Expert Comment

by:shambalad
ID: 24374509
boag2000 - Have you ever created a data-entry form with 30 different textboxes for a name field?
Personally, as someone who has been creating interactive screens for more than 25 years, I feel that my approach was comprehensive, robust, and flexible for future modifications. Nonetheless, I can understand titorober23's reluctance to take an approach he is not completely comfortable with, and do not object to titorober23's request.
Todd
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24378997
<boag2000 - Have you ever created a data-entry form with 30 different textboxes for a name field?>

No, but I don't think you specified that you had 30 characters.
As, perhaps, I may have reconsidered multiple textboxes as an option.
;-)
Also, I never had an issues with a mask like >L???.

Again, if you set the autotab property, then with every character they type, it will advance to the next box automatically. So the data entry speed is the same.
Then it's simply a matter of concatenating them together.

I do this for multi-part Field like Social Security Numbers.
I even now use this technique for dates, to avoid any chance for abiguity for dates like: 10/10/10

<I feel that my approach was comprehensive, robust, and flexible for future modifications.>
Fair enohgh, but you asked for comments and that was mine.
;-)

I am not arguing for any points here, I was just curious, (for my own education), as to what you finally decided on to solve this issue?

Jeff
0
 
LVL 7

Expert Comment

by:shambalad
ID: 24380418
Jeff -
I can see where the multiple textbox solution could work for fixed-length fields like SSNs and dates.
It's been my experience, though, with fields like names, one has to account for people pasting text into the textbox, or maybe setting the cursor somewhere in the middle of the textbox and then inserting or deleting a letter.  These types of operations could be problematic with a multiple textbox approach. Now if the users were not allowed to do these things, then all this would be moot. So I guess it boils down to what the particular requirements for the form are.
And yes, titorober23. I too am a little curious on the approach you have decided to use.
Regards,
Todd
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24380843
shambalad,

Yes, I agree with you totally.

I simply just suggested it as an alternative.

;-)

Jeff
0
 

Expert Comment

by:shapij
ID: 24843948
This may be closed but I certainly didn't like any of the answers.  I've done this many moons ago but couldn't quite remember how.  Nonetheless it didn't take but a few minutes to recreate:
Private Sub Form_KeyPress(KeyAscii As Integer)
'Make sure to set the form's KeyPreview property to 'Yes' to
'enable this event to fire.
    If KeyAscii > 96 And KeyAscii < 123 Then
       KeyAscii = KeyAscii - 32
    End If
End Sub

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question