textbox parameters for data entry

Fordraiders used Ask the Experts™
I have a text box on a form that needs the following requirements:

1. always UPPER CASE.
2. MUST be 5 characters(alphanumeric) in length.
    A. Can NOT end in "AA"

SSDAA  <<   will not work
DS333  <  -- GOOD
FGH56 <--- GOOD

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


ok for setting UPPERCASE
Public Sub ConvertToCaps(KeyAscii As Integer)
' Converts text typed into control to upper case

   Dim strCharacter As String
   ' Convert ANSI value to character string.
   strCharacter = Chr(KeyAscii)
   ' Convert character to upper case, then to ANSI value.
   KeyAscii = Asc(UCase(strCharacter))

End Sub

Open in new window

For setting character length:
Sub LimitFieldSize(KeyAscii, MAXLENGTH)
      Dim C As Control
      Dim CLen As Integer

      Set C = Screen.ActiveControl

      ' Exit if a non-printable character is typed.
      If KeyAscii < 32 Then Exit Sub

      ' Exit if typing replaces a selection.
      If C.SelLength > 0 Then Exit Sub

      ' Fetch length of current contents + 1 for the character typed.
      CLen = Len(C.Text & "") + 1

      ' Are there trailing spaces to contend with?
      If C.SelStart + 1 > CLen Then CLen = C.SelStart + 1

      ' Is length of string greater than max?
      If CLen > MAXLENGTH Then
        KeyAscii = 0
      End If

   End Sub

Open in new window

Calling the events:
Private Sub txtSubTrack_KeyPress(KeyAscii As Integer)
On Error Resume Next
LimitFieldSize KeyAscii, 5
ConvertToCaps KeyAscii
End Sub

Private Sub txtSubTrackName_KeyPress(KeyAscii As Integer)
'On Error Resume Next
'LimitFieldSize KeyAscii, 5
ConvertToCaps KeyAscii

End Sub

Open in new window

For now just need:
An alarm to tell me if the last 2 characters  are  "AA"  ?
Top Expert 2016
see if this will do what you want,
use the before update event of the textbox

private sub txtSubTrack_beforeupdate(cancel as integer)
if right(me.txtSubTrack,2)="AA" then
    msgbox "Last two characters not allowed!"
end if
end sub
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Presonally, I prefer to use the controls AfterUpdate event, or the Form_BeforeUpdate event to check for field validity.  By putting it in the Form_BeforeUpdate event, you allow the user to tab out of the control even if they have not finished entering the information (maybe they forgot a value and need to look it up).

Just my 2 cents.



Thanks very much !

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial