Solved

Input Mask, only letters all capital

Posted on 2009-05-05
12
3,928 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
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
 
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 100 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HasData 9 35
Export  Access Query To Excell 16 43
Listbox not remembering select items when come back to the record 5 27
MS Access Tables Linking 6 37
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

943 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

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now