Input Mask, only letters all capital

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
titorober23Asked:
Who is Participating?
 
titorober23Connect With a Mentor Author Commented:
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
 
shambaladCommented:
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
 
shambaladCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
shambaladCommented:
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
 
shambaladConnect With a Mentor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
Then for the record, can you state what your solution was?

My post suggested a "simplier" solution as well.
0
 
shambaladCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
shambaladCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
shambalad,

Yes, I agree with you totally.

I simply just suggested it as an alternative.

;-)

Jeff
0
 
shapijCommented:
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
All Courses

From novice to tech pro — start learning today.