Validation of e-mail entries in a hyperlink field, Access 2003

[Scott_C]

Hello, all!

I am a mostly self-taught access user (I've been to one training class but
it only re-hashed what I had already figured out on my own...need to go take
the level 2 class for new material I guess...) with a question:

I am trying to set up a validation rule that will force the entry of an
E-mail address in the format of mailto : // user @ location . ??? (spaces
inserted here to prevent hyperlinking of a fake e-mail address) I am using
the hyperlink data type to make the links clickable to open a new e-mail to
that contact in my default e-mail app (lotus notes)

This would be easy to do IF hyperlink fields had an input mask property, but
I don't see it in the list...so I'm trying validation rules.

I have tried various formats for the rule, most recently Is Null OR (Like
"mailto://?*@?*.?*")

I keep receiving validation errors on my existing data, as well as attempts
@ new entries. Where is my mistake? (and this doesn't include e-mail formats
such as name.name or company.??.?? etc...I'll probably need to add more or
statements to handle those...

 

[Brendan Reynolds]

Hello, all!

I am a mostly self-taught access user (I've been to one training class but
it only re-hashed what I had already figured out on my own...need to go
take
the level 2 class for new material I guess...) with a question:

I am trying to set up a validation rule that will force the entry of an
E-mail address in the format of mailto : // user @ location . ??? (spaces
inserted here to prevent hyperlinking of a fake e-mail address) I am using
the hyperlink data type to make the links clickable to open a new e-mail
to
that contact in my default e-mail app (lotus notes)

This would be easy to do IF hyperlink fields had an input mask property,
but
I don't see it in the list...so I'm trying validation rules.

I have tried various formats for the rule, most recently Is Null OR (Like
"mailto://?*@?*.?*")

I keep receiving validation errors on my existing data, as well as
attempts
@ new entries. Where is my mistake? (and this doesn't include e-mail
formats
such as name.name or company.??.?? etc...I'll probably need to add more or
statements to handle those...

Validating an e-mail address is no simple matter, I'm afraid. You could try
using a regular expression such as the one described at the following URL
....

http://www.regular-expressions.info/email.html

I seem to remember one of the MVPs having information on a web site about
using regular expressions in VBA, but I can't remember who it was. Anyone
else?

 

[Scott_C]

Validating an e-mail address is no simple matter, I'm afraid. You could try
using a regular expression such as the one described at the following URL
...

http://www.regular-expressions.info/email.html

I seem to remember one of the MVPs having information on a web site about
using regular expressions in VBA, but I can't remember who it was. Anyone
else?

Though it looks good on paper, I still can't translate that to an expression
that works in access. my most recent attempt:
Is Null Or "mailto://" & (Like
"[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?")

this still fails on any e-mail address...

 

[Dirk Goldgar]

Hello, all!

I am a mostly self-taught access user (I've been to one training class but
it only re-hashed what I had already figured out on my own...need to go
take
the level 2 class for new material I guess...) with a question:

I am trying to set up a validation rule that will force the entry of an
E-mail address in the format of mailto : // user @ location . ??? (spaces
inserted here to prevent hyperlinking of a fake e-mail address) I am using
the hyperlink data type to make the links clickable to open a new e-mail
to
that contact in my default e-mail app (lotus notes)

This would be easy to do IF hyperlink fields had an input mask property,
but
I don't see it in the list...so I'm trying validation rules.

I have tried various formats for the rule, most recently Is Null OR (Like
"mailto://?*@?*.?*")

I keep receiving validation errors on my existing data, as well as
attempts
@ new entries. Where is my mistake? (and this doesn't include e-mail
formats
such as name.name or company.??.?? etc...I'll probably need to add more or
statements to handle those...

Do you have to make it a hyperlink field? I prefer to use a text fields for
an e-mail address, and use the DblClick event of the text box displaying it
(on a form, of course) to execute code along these lines:

'----- start of code -----
Private Sub EmailAddress_DblClick(Cancel As Integer)

Dim strEmail As String

If Not IsNull(Me.EmailAddress) Then
strEmail = Me.EmailAddress
If Left(strEmail, 7) <> "mailto:" Then
strEmail = "mailto: " & strEmail
End If
Application.FollowHyperlink strEmail
End If

End Sub
'----- end of code -----

One trouble with hyperlink fields is that what you see is not what you get.
A hyperlink field is really a text field in a special format. The field is
broken into multiple parts, with '#' used as a separator, as follows:

displaytext#address#subaddress#screentip

All parts except the address are optional, but the separators will still be
present where necessary to distinguish which part is supplied. The
displaytext, if present, is what you see when you look at the field, while
the address is the actual URL.

If there is no display text. subaddress, or screentio, the minimal hyperlink
will consist of:

#address#

Depending on how it is entered, an email address would probably look like
one of these (with spaces inserted to keep the mailreader from "fixing it"):

#mailto: user @ domain . tld#
#mailto: user @ domain . tld#mailto: user @ domain . tld#

So your validation rule or code would have to allow for the '#' separators.
If you're using code to validate it, you could use the HyperlinkPart()
function to extract just the address (without the separators) and then
validate that.

I use this function to validate e-mail addresses entered in text fields:

'----- start of code -----
Function IsValidEmailAddress(Candidate As String) As Boolean

If Trim(Candidate) Like "?*@[!.]*.[!.]*" Then
If Not Candidate Like "*@*@*" Then
IsValidEmailAddress = True
End If
End If

End Function
'----- end of code -----

That is not conclusive, but gives a simple quick check. Possibly the
function should also check the length of the top-level domain; I have never
bothered with that.

 

[Scott_C]

Hello, all!

I am a mostly self-taught access user (I've been to one training class but
it only re-hashed what I had already figured out on my own...need to go
take
the level 2 class for new material I guess...) with a question:

I am trying to set up a validation rule that will force the entry of an
E-mail address in the format of mailto : // user @ location . ??? (spaces
inserted here to prevent hyperlinking of a fake e-mail address) I am using
the hyperlink data type to make the links clickable to open a new e-mail
to
that contact in my default e-mail app (lotus notes)

This would be easy to do IF hyperlink fields had an input mask property,
but
I don't see it in the list...so I'm trying validation rules.

I have tried various formats for the rule, most recently Is Null OR (Like
"mailto://?*@?*.?*")

I keep receiving validation errors on my existing data, as well as
attempts
@ new entries. Where is my mistake? (and this doesn't include e-mail
formats
such as name.name or company.??.?? etc...I'll probably need to add more or
statements to handle those...

Do you have to make it a hyperlink field? I prefer to use a text fields for
an e-mail address, and use the DblClick event of the text box displaying it
(on a form, of course) to execute code along these lines:

'----- start of code -----
Private Sub EmailAddress_DblClick(Cancel As Integer)

Dim strEmail As String

If Not IsNull(Me.EmailAddress) Then
strEmail = Me.EmailAddress
If Left(strEmail, 7) <> "mailto:" Then
strEmail = "mailto: " & strEmail
End If
Application.FollowHyperlink strEmail
End If

End Sub
'----- end of code -----

One trouble with hyperlink fields is that what you see is not what you get.
A hyperlink field is really a text field in a special format. The field is
broken into multiple parts, with '#' used as a separator, as follows:

displaytext#address#subaddress#screentip

All parts except the address are optional, but the separators will still be
present where necessary to distinguish which part is supplied. The
displaytext, if present, is what you see when you look at the field, while
the address is the actual URL.

If there is no display text. subaddress, or screentio, the minimal hyperlink
will consist of:

#address#

Depending on how it is entered, an email address would probably look like
one of these (with spaces inserted to keep the mailreader from "fixing it"):

#mailto: user @ domain . tld#
#mailto: user @ domain . tld#mailto: user @ domain . tld#

So your validation rule or code would have to allow for the '#' separators.
If you're using code to validate it, you could use the HyperlinkPart()
function to extract just the address (without the separators) and then
validate that.

I use this function to validate e-mail addresses entered in text fields:

'----- start of code -----
Function IsValidEmailAddress(Candidate As String) As Boolean

If Trim(Candidate) Like "?*@[!.]*.[!.]*" Then
If Not Candidate Like "*@*@*" Then
IsValidEmailAddress = True
End If
End If

End Function
'----- end of code -----

That is not conclusive, but gives a simple quick check. Possibly the
function should also check the length of the top-level domain; I have never
bothered with that.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks, Dirk!

This solves the problem for me...I never even thought of using the
double-click property (probably because the word "code" sends shivers up and
down my spine...lol)