auto populate e-mail address (1 Viewer)

Danny · May 18, 2017

I’ve the following combo & text fields:

1. cboAnalyst (dropdown)
analyst1
analyst2
analyst3

2.txtAnalystEmail (text field to enter e-mail address)

Depending on the selection made in cboAanlyst dropdown, I would like the txtAnalystEmail to auto populate the analyst’s e-mail address.

e.g.:
If selection in cboAlalyst = Analyst1, then I would like Analyst1@e-mail.com to populate in the txtAlalystEmail.

The way it’s currently set up is that the analyst manually enters his/her e-mail address in the text box.

TIA,

isladogs · May 18, 2017

Use 2 columns for the combo box row source
a) the first column is the analyst ID - this is the bound column
b) the 2nd column is the email address.

Set the width of the 2nd column = 0 so its hidden

Now add the following 2 lines as part of the cboAnalyst_AfterUpdate event:

Code:

Me.txtAnalystEmail = Me.cboAnalyst.Column(1)
Me.txtAnalystEmail.Requery

The requery will ensure the email is updated when the combo box value is changed

NOTE: In case you didn't know, the first column is Column(0) so the 2nd column is Column(1)

Danny · May 19, 2017

ridders, thanks for the reply post.

The row source of cboAnalyst has the following:

AnalystID
AnalystName
Password (criteria: like “ad”)
Status

criteria: like “A”)

However, the e-mail address is not stored in a table. Is it possible to modify the code you provided such that if analyst1 is selected in cboAnalyst, txtAnalystEmail=” analyst1@email.com etc.?

TIA

BigHappyDaddy · May 19, 2017

Danny, what is the relationship between AnalystID and the email address? If you are not storing the address anywhere, how are you expecting the address to be auto-populated?

isladogs · May 19, 2017

Danny said:
ridders, thanks for the reply post.

The row source of cboAnalyst has the following:

AnalystID
AnalystName
Password (criteria: like “ad”)
Statuscriteria: like “A”)

However, the e-mail address is not stored in a table. Is it possible to modify the code you provided such that if analyst1 is selected in cboAnalyst, txtAnalystEmail=” analyst1@email.com etc.?

TIA

Sorry about the delay responding - problem accessing the site all day!

Yes, just add a 5th column to the combobox i.e. Column(4)
- it should be hidden.

This will create the email as follows
Email: [AnalystID] & "@email.com"
(replacing with the actual email address 'stem')

The code I gave you before as part of the cboAnalyst_AfterUpdate event should now be:

Code:

Me.txtAnalystEmail = Me.cboAnalyst.Column(4)
Me.txtAnalystEmail.Requery

Danny · May 22, 2017

ridders,

I’m not sure if I followed your instruction correctly but following is what I have:

Expr1: Column(4)
Criteria: Email: [AlalystID]]" & "@CompanyName.com"

When I attempted to run the query, I get the following error:

The expression you entered contains invalid syntax. You may have entered an operand without an operator.

(replacing with the actual email address 'stem')

Can you please clarify the above including ‘sem’)?

As for the e-mail, it is set up as follows:
AnalystfirstName.AnalystLastName@CompanyName.com

Please let me know if you have any questions.

TIA

isladogs · May 22, 2017

ridders,

I’m not sure if I followed your instruction correctly but following is what I have:

Expr1: Column(4)
Criteria: Email: [AlalystID]]" & "@CompanyName.com"

When I attempted to run the query, I get the following error:

The expression you entered contains invalid syntax. You may have entered an operand without an operator.

Probably just a typo but you have 2 "]" in [AlalystID]] and the field name is wrong

Can you please clarify the above including ‘sem’)?

The 'stem' is the common part of the email address - everything from @ onwards e.g. @CompanyName.com

As for the e-mail, it is set up as follows:
AnalystfirstName.AnalystLastName@CompanyName.com

Just modify the code used for the final column of the combo box
Assuming you have 2 separate fields AnalystfirstName & AnalystLastName then it should be:

[AnalystfirstName] & "." & [AnalystLastName] & "@CompanyName.com"

or if all lower case, then

LCase([AnalystfirstName] & "." & [AnalystLastName] & "@CompanyName.com")

However in post #3, you said the 2nd column was AnalystName
If the first name & last name are stored in one field, you'll need to separate them using a query or function to find a space in the name
e.g. AnalystfirstName = Left([AnalystName],InStr([AnalystName], " "))
AnalystLastName = Mid([AnalystName],InStr([AnalystName], " ")+1)

Things will of course go 'pear shaped' with names like Anne Marie Jones

Danny · May 22, 2017

ridders, that’s correct the second column is the AnalystName and both first and last names are stored in this field.
So Column(4) now should look like as follows, correct?

Email: Left([AnalystName],InStr([AnalystName], " ")) & "." & LCase([AnalystfirstName] & "." & [AnalystLastName] & "@CompanyName.com")

TIA,

isladogs · May 22, 2017

Danny said:
ridders, that’s correct the second column is the AnalystName and both first and last names are stored in this field.
So Column(4) now should look like as follows, correct?

Email: Left([AnalystName],InStr([AnalystName], " ")) & "." & LCase([AnalystfirstName] & "." & [AnalystLastName] & "@CompanyName.com")

TIA,

NO!!! It should be:

Code:

Email : LCase(Left([AnalystName],InStr([AnalystName], " "))  &  "." & Mid([AnalystName],InStr([AnalystName], " ")+1) & "@CompanyName.com")

BUT as I hinted in the last post, I'd STRONGLY recommend you add 2 new fields to store the first name & last name.

Otherwise, you WILL have problems with certain names like Anne Marie Jones where the above code will treat the last name as Marie Jones.
This will create an email of anne.marie_jones@companyname.com .... complete with a space as shown
It is possible to remove the space with code but the error will remain

However, if you do create 2 new fields, you can just use these new fields to create the email address

Danny · May 23, 2017

Colin,
Many thanks for the solution you provided!

Regards,

auto populate e-mail address (1 Viewer)

Danny

Registered User.

isladogs

MVP / VIP

Danny

Registered User.

BigHappyDaddy

Coding Monkey Wanna-Be

isladogs

MVP / VIP

Danny

Registered User.

isladogs

MVP / VIP

Danny

Registered User.

isladogs

MVP / VIP

Danny

Registered User.

Similar threads

Users who are viewing this thread