Solved

Access Code to check and convert name to email address

Posted on 2010-11-17
6
555 Views
Last Modified: 2012-05-10
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 firstname.lastname@maildomain.com into the email field (txtLSSemail)

Any assistance would be appreciated.

Thanks!
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))) & "@maildomain.com"
    End If
End Sub

Open in new window

0
Comment
Question by:shogun5
  • 3
  • 2
6 Comments
 
LVL 84
ID: 34154074
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 & "@maildomain.com"

0
 

Author Comment

by:shogun5
ID: 34154249
LSMConsulting:

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:
susan.smith@emaildomain.com

Hope this explains my situation better. Thanks!
0
 
LVL 6

Accepted Solution

by:
YohanF earned 250 total points
ID: 34154569
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 
    Else
        'code if only one name without any spaces in between
    End If

Open in new window


Let me know if you need more help!
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:shogun5
ID: 34154669
YohanF:

How do I add the reference? Kind of new at this...
0
 
LVL 6

Expert Comment

by:YohanF
ID: 34154689
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!
0
 

Author Closing Comment

by:shogun5
ID: 34154873
Yep! That did! Thanks!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

777 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