Access Code to check and convert name to email address

Hello Experts,

Need a little help on this code.

I have a name field on my form (txtLSSname). I am trying to write code to do the following.

1. Check to see if there is a name in the field
2. If there is a name in the field check to see if both first and last name was entered. If not, display a message asking using to enter a last (second name) as well as the first name.
3. If both names are present put name into email format like into the email field (txtLSSemail)

Any assistance would be appreciated.

Private Sub txtLSSname_AfterUpdate()
    If Not (LTrim(Mid(txtLSSname, InStr(1, txtLSSname, " ") + 1)) = 0) Or Not (Left(Me.txtLSSname, InStr(1, Me.txtLSSname, " ") - 1)) = 0 Then
            Me.txtLSSemail = LCase(Left(Me.txtLSSname, InStr(1, Me.txtLSSname, " ") - 1)) & "." & LCase(LTrim(Mid(txtLSSname, InStr(1, txtLSSname, " ") + 1))) & ""
    End If
End Sub

Open in new window

Who is Participating?
YohanFConnect With a Mentor Commented:
Try this, this is a different approach - using regular expression. Before using make sure you add the reference "Microsoft VBScript Regular Expressions 5.5"

    Dim reEx As RegExp
    Set reEx = New RegExp
    reEx.Pattern = "\b[A-Z]+[\s]+[A-Z]+\b"
    reEx.IgnoreCase = True
    If reEx.Test(txtLSSname) = True Then
        'code when 2 or more names present seperated by a space 
        'code if only one name without any spaces in between
    End If

Open in new window

Let me know if you need more help!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try this. You'll have to change the names of the controls and such in order for this to work:

If Nz(Me.YourFirstNameField,"") = "" Then
  Msgbox "You must enter a First name"
  Exit Sub
End If

Me.txtLSSEmail = Me.YourFirstNameField & "." & Me.YourLastNameField & ""

shogun5Author Commented:

I only have one name field (txtLSSname) on the form. So, for example,
if the user does not enter anything in the name field - that's okay - nothing will populate into the txtLSSemail field. If, however, the user enters one name into the name field, i.e. Susan, then I should have code to detect that there is no value following Susan hence the user only entered one name in the name field. I would like at that point to display a message (msgbox "Please enter your last name as well" (I will assume the user entered in the first name only).

If the user then enters in the first and last name into the name field (txtLSSname), i.e. Susan Smith, the after update event will populate the txtLSSemail textbox with the following value:

Hope this explains my situation better. Thanks!
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

shogun5Author Commented:

How do I add the reference? Kind of new at this...
When you are in VBA (or the coding part - visual basic window), go to Tools Menu > References.

From that list box search for the "Microsoft VBScript Regular Expressions 5.5" and put a tick to that. Then its done!
shogun5Author Commented:
Yep! That did! Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.