Restricted text

J

JayTee

Hi all,
Can someone please tell me the code to ensure a user enters only the
following combination of text and Numbers. (i.e. JT0982) I have a Access
front linked to an SQL back end.
I know there is a mask but this won't help me because of the back end being
SQL and I don't know how to set the required text combination in SQL so I
thought I would see if I can obtain a bit of code to use on a text boxes
after update event.
The bottom line is, if some enters anything else other then the combination
of 2 letters followed by 4 numbers with no spaces a message will pop up
saying "Invalid Sign on" All signons on this database cosist of the above
combination.
Thanks in advance for anyone that may be able to assist me.

J
 
M

missinglinq via AccessMonster.com

Something like this should do it, replacing YourControl with the actual name
of your control:

Private Sub YourControl_BeforeUpdate(Cancel As Integer)
Dim I As Integer
Dim Hits As Integer
Hits = 0

'This insures that string is AA1234 format

For I = 1 To 2
If IsNumeric(Mid(Me.YourControl, I, 1)) Then
Hits = Hits + 1
End If
Next I

For I = 3 To 6
If Not IsNumeric(Mid(Me.YourControl, I, 1)) Then
Hits = Hits + 1
End If
Next I


If Hits > 0 Then
MsgBox "This field must be in the format AA1234 with no spaces!"
Cancel = True
Me.YourControl.SelStart = 0
Me.YourControl.SelLength = Len(Me.YourControl)
End If
End Sub
 
Top