Solved

Input Mask, only letters all capital

Posted on 2009-05-05
12
3,920 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

757 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

19 Experts available now in Live!

Get 1:1 Help Now