Solved

Access Code to check and convert name to email address

Posted on 2010-11-17
6
554 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

911 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

24 Experts available now in Live!

Get 1:1 Help Now