Text only field using Validation and/or input masks

[Twigathy]

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]

Put this 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

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

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

 

[Twigathy]

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:

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:

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]

Sorry, my mistake change this line:

If intCode < 65 And intCode > 90 Then

to:

If intCode < 65 Or intCode > 90 Then

 

[Twigathy]

Woohoo!

Thank you very much, now its working nicely