textbox parameters for data entry

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FordraidersAuthor Commented:
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"  ?
Rey Obrero (Capricorn1)Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Dev-Soln LLCCommented:
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.

FordraidersAuthor Commented:
Thanks very much !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.