Text only field using Validation and/or input masks

Twigathy · Feb 17, 2004

Hi,

I have a field in my database that I only want the end user to be able to enter in text. That is no numerics at all, just the letters a to z. How would I do this, and what would it involve, I am guessing some form of validation.

Would this also work if the validation is being done at the table-level in a form (Users enter data through forms)

Thanks

Twigathy

Mile-O · Feb 17, 2004

Put this in a module:

Code:

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

In the BeforeUpdate event of your textbox you can write this (where txtExample is the name of your textbox.

Code:

If Not IsAlpha(Me.txtExample) Then
    MsgBox "Textbox contains illegal characters.", vbExclamation
    Me.txtExample.Undo
    Cancel = True
End If

Twigathy · Feb 17, 2004

Thanks for the quick answer, although I still cant get it to work [Silly newbie to access that I am].

I have this on the Before Update on the textbox I want to check:

Code:

Private Sub Text31_BeforeUpdate(Cancel As Integer)
If Not IsAlpha(Me.Text31) Then
    MsgBox "Textbox contains illegal characters.", vbExclamation
    Me.Text31.Undo
    Cancel = True
End If
End Sub

and this in a module called IsAlpha:

Code:

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

The only problem is that when I type in numerics for it to validate, it gives me a compile error (Expected variable or proceedure). I thought it may be getting confused because there is a module called IsAlpha, so i renamed the module AlphaCheck, and now it doesnt do anything when I enter numbers

What am I doing wrong here?

Thanks

Twigathy

Mile-O · Feb 17, 2004

Sorry, my mistake change this line:

Code:

If intCode < 65 And intCode > 90 Then

to:

Code:

If intCode < 65 Or intCode > 90 Then

Twigathy · Feb 17, 2004

Woohoo!

Thank you very much, now its working nicely

Text only field using Validation and/or input masks

Twigathy

Guest

Mile-O

Back once again...

Twigathy

Guest

Mile-O

Back once again...

Twigathy

Guest

Similar threads

Users who are viewing this thread