Validating E-mail addresses

  • Thread starter Thread starter Bob Richardson
  • Start date Start date
B

Bob Richardson

This seems to be a common problem. I placed the following code in a form
where most users will be entering e-mail addresses. I'm sure it can be
improved on, especially in the area of "invalid characters." But this
approach won't work if data is entered on a datasheet. Is there a way to
code something in the "Validation Rule" area in order to trap these errors?

Private Sub Email_BeforeUpdate(Cancel As Integer)
If Me!EmailAdr Like "*[ !$%^&*()]*" Then
MsgBox "Invalid characters in EMail address", vbOKOnly
Cancel = True
End If

If Not (Me!EmailAdr Like "*@*.*") Then
MsgBox "EMail should be in the format (e-mail address removed)", vbOKOnly
Cancel = True
End If
End Sub
 
Have you tried something like:

(Like "*@*.*" And Not Like "*[ !$%^&*()]*") Or Is Null

in the Validation Rule of the Field in the DesignView of
the Table?

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
This seems to be a common problem. I placed the following code in a form
where most users will be entering e-mail addresses. I'm sure it can be
improved on, especially in the area of "invalid characters." But this
approach won't work if data is entered on a datasheet. Is there a way to
code something in the "Validation Rule" area in order to trap these errors?

Private Sub Email_BeforeUpdate(Cancel As Integer)
If Me!EmailAdr Like "*[ !$%^&*()]*" Then
MsgBox "Invalid characters in EMail address", vbOKOnly
Cancel = True
End If

If Not (Me!EmailAdr Like "*@*.*") Then
MsgBox "EMail should be in the format
Click to expand...
(e-mail address removed)", vbOKOnly
 
Seems to work perfectly. Thanks a ton.

When I applied it to my existing DB, I got one error message indicating that
there was a problem with an existing e-mail address. Since I got just one
message, I presume there was only one bad e-mail in the file (only 1700
records). Is that correct?

Is there a way to find out which record had the error? What happened to the
e-mail address for that record?

Van T. Dinh said:
Have you tried something like:

(Like "*@*.*" And Not Like "*[ !$%^&*()]*") Or Is Null

in the Validation Rule of the Field in the DesignView of
the Table?

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
This seems to be a common problem. I placed the following code in a form
where most users will be entering e-mail addresses. I'm sure it can be
improved on, especially in the area of "invalid characters." But this
approach won't work if data is entered on a datasheet. Is there a way to
code something in the "Validation Rule" area in order to trap these errors?

Private Sub Email_BeforeUpdate(Cancel As Integer)
If Me!EmailAdr Like "*[ !$%^&*()]*" Then
MsgBox "Invalid characters in EMail address", vbOKOnly
Cancel = True
End If

If Not (Me!EmailAdr Like "*@*.*") Then
MsgBox "EMail should be in the format
Click to expand...
(e-mail address removed)", vbOKOnly
Cancel = True
End If
End Sub


.
Click to expand...
Click to expand...
 
There may be more than one Record that has bad e-mail address.

I guess you can use a select Query with the negation of the validation rule
as the selection criteria to select Records that have bad e-mail addresses.

The criteria should be:

(Not Like "*@*.*") Or (Like "*[ !$%^&*()]*")
 
I have a field where my users will need to enter the Last Name, First Name.
What validation rule can i put in place to ensure that enter Last Name, First
Name.
I want the values in this field to only have char and not numbers. Also no
special Char.


Field: ( Pats, Mickey)
 
I doubt you can. You can very that it contains a comma, but that is about
it.

What about "Smith, Bobby Jo"

Or "Thorne Smith, Courtney"

Or "Johnson, J.R."

Lots of variables with names.

About the best you could do is verify that there is a comma and verify that
the length is at least X characters. I knew a guy who's last name was "O".
The airline computers required a length of two characters or more, so all
his tickets said "OO" :-)


Rick B
 
note, you can use the input mask to eliminate special characters and
numbers, but then you run into that comma.

If it were me, I'd have two separate fields. One for first, and one for
last. You will stull not be able to get rid of special characters.
O'Conner


Rick B
 
Rick my friend suggested this but any code suggestions to making sure they
have a comma in the field. Like here is what my friend said to try.

=InStr([contact response 1].[Value],",")>0 And InStr([contact response
1].[Value],",")<Len([contact response 1].[Value])

Contact response is my field for Last name and first name.
 
Back
Top