Calling a Function

Posted on 2012-09-17
Last Modified: 2012-09-17
Dear Experts,
Its early in the morning & I have a rush job one & am having a thick moment ...

I have a form which is used for entering subscribers details (name, email address etc) & I am putting in a piece of code to validate the email address first.  

I have copied a piece of code courtesy of EE for a function which checks the email address for common syntax errors.  This is a function called ValidEmail.

I need to get this function called as part of of the code contained in the button "Add" which is below. The check needs to run against the field "AddEmail"

Add Code:

Private Sub Add_Click()
' ********* Check Bit  *********
If Len(Me!AddEmail & "") = 0 Then
       MsgBox "You must add at least an Email Address", vbExclamation, "Warning !"
        Cancel = True
        Exit Sub
    End If
'  *****************Need to add the check here ******************

' ********* Save Bit  *********
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("WeeklyMembers")
rs!Email = Me.AddEmail
rs!Person = Me.Personname
rs!Firm = Me.Firm
rs!Role = Me.Role
rs![Subscriber Since] = Now()

If Len(Me!Personname & "") = 0 Then
MsgBox (Me.AddEmail & " has been added"), , "Success"
MsgBox (Me.Personname & " has been added"), , "Success"
End If
Me.AddEmail = ""
Me.Personname = ""
Me.Firm = ""
Me.Role = ""
End Sub

Open in new window

The Function ValidEmail code is below:

Function ValidEmail(ByVal strCheck As String) As Boolean
'Created by Chad M. Kovac
'Tech Knowledgey, Inc.

Dim bCK As Boolean
Dim strDomainType As String
Dim strDomainName As String
Const sInvalidChars As String = "!#$%^&*()=+{}[]|\;:'/?>,< "
Dim i As Integer

bCK = Not InStr(1, strCheck, Chr(34)) > 0 'Check to see if there is a double quote
If Not bCK Then GoTo ExitFunction

bCK = Not InStr(1, strCheck, "..") > 0 'Check to see if there are consecutive dots
If Not bCK Then GoTo ExitFunction

' Check for invalid characters.
If Len(strCheck) > Len(sInvalidChars) Then
    For i = 1 To Len(sInvalidChars)
        If InStr(strCheck, Mid(sInvalidChars, i, 1)) > 0 Then
            bCK = False
            GoTo ExitFunction
        End If
    For i = 1 To Len(strCheck)
        If InStr(sInvalidChars, Mid(strCheck, i, 1)) > 0 Then
            bCK = False
            GoTo ExitFunction
        End If
End If

If InStr(1, strCheck, "@") > 1 Then 'Check for an @ symbol
    bCK = Len(Left(strCheck, InStr(1, strCheck, "@") - 1)) > 0
    bCK = False
End If
If Not bCK Then GoTo ExitFunction

strCheck = Right(strCheck, Len(strCheck) - InStr(1, strCheck, "@"))
bCK = Not InStr(1, strCheck, "@") > 0 'Check to see if there are too many @'s
If Not bCK Then GoTo ExitFunction

strDomainType = Right(strCheck, Len(strCheck) - InStr(1, strCheck, "."))
bCK = Len(strDomainType) > 0 And InStr(1, strCheck, ".") < Len(strCheck)
If Not bCK Then GoTo ExitFunction

strCheck = Left(strCheck, Len(strCheck) - Len(strDomainType) - 1)
Do Until InStr(1, strCheck, ".") <= 1
    If Len(strCheck) >= InStr(1, strCheck, ".") Then
        strCheck = Left(strCheck, Len(strCheck) - (InStr(1, strCheck, ".") - 1))
        bCK = False
        GoTo ExitFunction
    End If
If strCheck = "." Or Len(strCheck) = 0 Then bCK = False

ValidEmail = bCK
End Function

Open in new window

Can Anybody Help?
Question by:correlate
    LVL 29

    Accepted Solution

    Try this:

    If Not ValidEmail(Me.AddEmail) Then
        MsgBox "You need to enter a valid email address.", vbExclamation, "Invalid Email"
        Cancel = True
        Exit Sub

    Open in new window


    Author Closing Comment

    Brilliant Thank you & thank you for the speedy response

    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

    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…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
    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…

    732 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

    22 Experts available now in Live!

    Get 1:1 Help Now