How do I validate my form to only allow Alphabetic characters, not numbers?

Hello Kiwi_coder,

You can add the UDF below to a regular VBA module, and then use a snippet like this in your form (perhaps
on the BeforeUpdate event of the textbox(es) you need to check):

If RegExpFind(Me!FirstName, "[^a-z]", 1, False) <> "" Then
    MsgBox "First Name can only have A-Z"
    Cancel = True
End If


Regards,

Patrick

Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True) 
    ' For more info see: http://vbaexpress.com/kb/getarticle.php?kb_id=841 
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 0                   : the last match
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
    ' returns an empty string.  If no match is found, the function returns an empty string
    
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    
    Static RegX As Object
    Dim TheMatches As Object
    Dim Answer() As String
    Dim Counter As Long
    
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
    
    ' Create instance of RegExp object
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
    End With
        
    ' Test to see if there are any matches
    If RegX.test(LookIn) Then
        
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        Set TheMatches = RegX.Execute(LookIn)
        
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1) As String
            For Counter = 0 To UBound(Answer)
                Answer(Counter) = TheMatches(Counter)
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        Else
            Select Case Pos
                Case 0                          ' Last match
                    RegExpFind = TheMatches(TheMatches.Count - 1)
                Case 1 To TheMatches.Count      ' Nth match
                    RegExpFind = TheMatches(Pos - 1)
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    
    ' If there are no matches, return empty string
    Else
        RegExpFind = ""
    End If
    
    ' Release object variables
    Set TheMatches = Nothing
    
End Function

Select allOpen in new window

Kiwi_coder,

BTW, what about 2+ word names, periods, and/or hyphens?

Jill St. John

Lotho Sackville-Baggins

My suggestion above would make those names appear invalid, as I took your instructions about letters-only
very literally...

Regards,

Patrick

Thank you for your quick response.  I see your point about the hyphens and full stops, that is something that I hadn't considered.
Is there another way which does not involve the use of VBA? I'm not very confident in the use of it in Access and have managed not to use it so far.  Is there a common set of code that I could add to the Validation Rule section of the field's properties that would accomplish the same thing?
p.s. feel free to call me silly names if I've missunderstood your solution.
Regards,
Kiwi

Kiwi_coder said:
>>Thank you for your quick response.  I see your point about the hyphens and full stops, that is something that
>>I hadn't considered.

And spaces.

Billy Bob Thornton
Oscar de la Hoya

>>Is there another way which does not involve the use of VBA?

In this case, I doubt it, but perhaps another Expert could show how...

>>p.s. feel free to call me silly names if I've missunderstood your solution.

Never!  :)

The code I supplied above will look for the first character in the textbox that is *not* a letter.  If it finds no such
character, then RegExpFind returns a zero-length string.  Thus, if RegExpFind returns anything other than ""
then we know the entry in the textbox is invalid.  Setting Cancel = True has the impact of cancelling the update
on the textbox.

Thank you for your help but whatever I do, I can't get it to act on the code you supplied :(
I will keep trying but in the meantime, does anyone else have any other potential solutions?
 
Regards,
Kiwi

Kiwi
You will not be able to do this without some vba script.

To employ my solution you will need to do the following
a) Create a new module, add the following Function GetAlphaNumeric to it and save it.
b) Fire the following code from the on exit events of your textboxes (I assume named txtFirstName,txtSurname).

sub txtFirstName_Exit(Cancel as integer)
    If IsNull(Me.txtFirstName.Value) = False Then Me.txtFirstName.Value = GetAlphaNumeric(Me.txtFirstName.Value)
end sub

sub txtSurname_Exit(Cancel as integer)
    If IsNull(Me.txtSurname.Value) = False Then Me.txtSurname.Value = GetAlphaNumeric(Me.txtSurname.Value)
end sub

Public Function GetAlphaNumeric(ByVal sInputString As String) As String
 
     Dim sAccept As String
     sAccept = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
     Dim s As String
     If Len(sInputString) > 0 Then
        Dim i As Long
        i = Len(sInputString)
        Dim x As Long
        For x = 1 To i
            If InStr(sAccept, Mid(sInputString, x, 1)) > 0 Then
                    s = s & (Mid(sInputString, x, 1))
            End If
        Next x
     Else
        s = ""
     End If
     GetAlphaNumeric = s
 
End Function

Select allOpen in new window

Note line 4 of code
sAccept = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"

You can modify this line to accept additional characters as you require.

Well, it semi works. It accepts anything that is pernmitted but when I try to input anything that isn't permitted it brings up a Microsoft Visual Basic Run-time error '3315': Field'Customers.FirstName' cannot be a zero-length string.

Any solutions to that?

or try something like this

sub txtFirstName_Exit(Cancel as integer)
   If IsNull(Me.txtFirstName.Value) = False
       Dim s as string = GetAlphaNumeric(Me.txtFirstName.Value)
       If s="" then
           Me.txtFirstName.Value = Null
        else
            me.txtFirstName.value = s
        End if
    End If
end sub

i mean like this....

sub txtFirstName_Exit(Cancel as integer)
   If IsNull(Me.txtFirstName.Value) = False then
       Dim s as string
       s = GetAlphaNumeric(Me.txtFirstName.Value)
       If s="" then
           Me.txtFirstName.Value = Null
        else
            me.txtFirstName.value = s
        End if
    End If
end sub

Thank you very much, it works like a charm. You were right, the table was set so that it did not allow zero values. If I may slip a last small inquiry onto the end of this question, how would I get it to create a message box telling the user what is wrong?  

Thank you for your help.

Regards,
Kiwi

this is a simple way of doing this
place this codes in the keydown event of the textbox

Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
    Case 96 To 105, 48 To 57
        KeyCode = 0
       
End Select

this is a simple way of doing this
place this codes in the keydown event of the textbox

Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
    Case 96 To 105, 48 To 57
        KeyCode = 0
       
End Select

End sub

sub txtFirstName_Exit(Cancel as integer)
   If IsNull(Me.txtFirstName.Value) = False then
       Dim s as string
       s = GetAlphaNumeric(Me.txtFirstName.Value)
       If s="" then
           Me.txtFirstName.Value = Null
           Call msgbox("You must enter an alpha numeric value for first name.")
           me.txtFirstName.setfocus()
        else
            me.txtFirstName.value = s
        End if
    End If
end sub