Validation Rule on field (a-z only characters)

[ignite]

Hello,

I'm trying to make it so that my Student Forename field only allows text (from a-z i.e. no numbers etc) to be written inside the box, would I use a certain validation rule and if so how would I do this please?

Thanks in advance.

 

[Valerie]

Try using Input Mask instead

 

[ignite]

Thanks - what type of input mask please?

 

[Mile-O]

Asc(LCase([MyField])) Between 65 And 90

is a simple way of doing it.


You can use a function in a module:

Public Function IsAlpha(ByVal strText As String) As Boolean
    On Error GoTo Err_IsAlpha
    Dim intCounter As Integer
    Dim intCode As Integer
    strText = StrConv(strText, vbUpperCase)
    For intCounter = 1 To Len(strText)
        intCode = Asc(Mid(strText, intCounter, 1))
        If intCode < 65 And intCode > 90 Then
            Exit Function
        End If
    Next intCounter
    IsAlpha = True
Exit_IsAlpha:
    Exit Function
Err_IsAlpha:
    IsAlpha = False
    Resume Exit_IsAlpha
End Function

Then, in the BeforeUpdate event of your textbox.

Private Sub MyTextbox_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.MyTextbox) Then Exit Sub
    If IsAlpha(Me.MyTextbox) = False Then
        MsgBox "The name you have entered contains non-alphabetical characters.", vbExclamation, "Warning!"
        Cancel = True
    End If
End Sub

 

[ignite]

Thank you but I get the following error:

Compile Error:

Expected variable or procedure, not module

I currently have a module called 'IsAlpha' with the following inside:

Public Function IsAlpha(ByVal strText As String) As Boolean
    On Error GoTo Err_IsAlpha
    Dim intCounter As Integer
    Dim intCode As Integer
    strText = StrConv(strText, vbUpperCase)
    For intCounter = 1 To Len(strText)
        intCode = Asc(Mid(strText, intCounter, 1))
        If intCode < 65 And intCode > 90 Then
            Exit Function
        End If
    Next intCounter
    IsAlpha = True
Exit_IsAlpha:
    Exit Function
Err_IsAlpha:
    IsAlpha = False
    Resume Exit_IsAlpha
End Function

And I added the BeforeUpdate bit as code with the following:

Private Sub Forename_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.Forename) Then Exit Sub
    If IsAlpha(Me.Forename) = False Then
        MsgBox "The name you have entered contains non-alphabetical characters.", vbExclamation, "Warning!"
        Cancel = True
    End If
End Sub

What have I done wrong please?

 

[Valerie]

Thanks - what type of input mask please?

You could try something like
>L<?????????????????????????

which gives you a capital first letter and then upto 25 lowercase letters

 

[Mile-O]

What line does it stop at?

Did you put the function in a standalone module (preferred) or a form's module (not advised) ?

 

[ignite]

It appears to stop at:

If IsAlpha(Me.Forename) = False Then

Did you put the function in a standalone module (preferred) or a form's module (not advised) ?

I just created a new module?

(Thanks.)

 

[Mile-O]

Change this line:

    On Error GoTo Err_IsAlpha

to

    ' On Error GoTo Err_IsAlpha

Then run the code again and see if it stops at a new line...

 

[ignite]

Still the same :-(

Any chance I could upload the Database for you to view please?

 

[Mile-O]

Yeah, open a new database and import the form and the module into it then upload that.

 

[ignite]

Thanks,

It's a bit difficult to copy and paste the information into a new database due to the amount of tables etc - best just to give you the exact replica I think.

When it loads simply press 'Continue' and select 'Student Details' and try and add a new record by pressing the 'Add Record' button.

The form I'm trying to get the module to recognise is 'FrmStudentDetails' and the textbox 'Forename'.

The file can be obtained here

Thank you very much !

 

[___]

Try changing the modules name.

 

[Mile-O]

As usual part of it is my fault: the code needs one line changed

If intCode < 65 And intCode > 90 Then

to

If intCode < 65 Or intCode > 90 Then

You should learn about the Option Explicit though. Your module never had any Option Compare either.

 

[ignite]

Thank you - could you upload the revised copy of my file so I can download it please.

Otherwise E-mail me it please - tingram@ingserv.com

Thank you !

 

[Mile-O]

All I did was compact & repair the database, change the line above, and put:

Option Explicit
Option Compare Database

at the top of each module - form and standalone.

That's all; I have nowhere to upload it to and I prefer not to email directly (people might find out who I really am! )

 

[Kodo]

that looks like an awful lot more work than is necessary.
Make reference to the vb5.5 libaray and use regular expressions.

Function checkalpha(mystring As String) As integer

Dim ObjRegExp As Object

Set ObjRegExp = New RegExp
ObjRegExp.Global = True
ObjRegExp.ignorecase = True
ObjRegExp.pattern = "[a-zA-Z]"

If ObjRegExp.test(mystring) = False Then
      checkalpha = 0
Else
      checkalpha = 1
End If

Set ObjRegExp = Nothing

End Function

put that in a module then run an if....then in your form: something like this.

If Not IsNull(Me.Text2) Then
    If checkalpha(Me.Text2) = 0 Then
       Me.Text2 = Null
    End If
End If

Easier validation. lots less code. no looping and much faster.

 

[ignite]

I cannot still get it to work Mile-O-Phile :-(

What happens on the revised copy of my DB you have please?

Thank you Kodo I have sent you a private message

 

[Kodo]

you can't name the module the same as the function.. well, you can but it's a BAD idea. Change the name of the module to something else.

NEXT: when you're in the VBE go to tools, references and look for Microsoft VBScript Regular Expressions 5.5 and check that box. You MUST do this inorder for the RegExp object to be instantiated.