Solved

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

Posted on 2009-04-04
17
237 Views
Last Modified: 2013-11-28
Hi, I have a form into which my user will be able to enter client details. I'm having trouble getting my first name and surname fields to only accept alphabetic characters. Does anyone know how I can fix this?

Regards,
Kiwi
0
Comment
Question by:Kiwi_coder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 3
  • +1
17 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24067216
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

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24067226
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
0
 

Author Comment

by:Kiwi_coder
ID: 24067238
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24067285
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.
0
 

Author Comment

by:Kiwi_coder
ID: 24067459
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
0
 
LVL 11

Expert Comment

by:mildurait
ID: 24067575
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

Open in new window

0
 
LVL 11

Expert Comment

by:mildurait
ID: 24067583
Note line 4 of code
sAccept = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"

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

Author Comment

by:Kiwi_coder
ID: 24067680
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.
0
 

Author Comment

by:Kiwi_coder
ID: 24067684
Any solutions to that?
0
 
LVL 11

Accepted Solution

by:
mildurait earned 500 total points
ID: 24067691
The error is caused by a constraint on your table.
Try something changing s="" to s="?" or remove the constraint on your table.



0
 
LVL 11

Expert Comment

by:mildurait
ID: 24067710
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
0
 
LVL 11

Expert Comment

by:mildurait
ID: 24067712
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
0
 

Author Comment

by:Kiwi_coder
ID: 24067746
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?  
0
 

Author Closing Comment

by:Kiwi_coder
ID: 31566571
Thank you for your help.

Regards,
Kiwi
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24067762
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


0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24067765
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
0
 
LVL 11

Expert Comment

by:mildurait
ID: 24070460
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
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question