[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Access Code to check and convert name to email address

Posted on 2010-11-17
6
Medium Priority
?
564 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 85
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 1000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

650 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