Solved

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

Posted on 2009-04-04
17
233 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
  • 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now