Autofill combobox based on input of another combobox.

[CJPHX]

Good Afternoon all,

I currently have a combo box on a form that reads from a linked table. The linked table, tbl_Requestor, contains two columns one with the employees name the other with the employees e-mail address. When filling out a form to submit a request they select their name from the drop down box. I would like a box for the e-mail address to populate based on the name selected in the first box. I have taken over this database from another individual and am trying to update it some and am fairly new to this. This is the code being used for the first combobox, cboRequestorName, where the employee selects their name from.

SELECT tbl_Requestor.RequestorName FROM tbl_Requestor ORDER BY tbl_Requestor.RequestorName;

I currently have the combobox, Combo45 where the E-mail address would go but it is just blank.



Any help would be greatly appreciated.

Thank you,
CJ

[June7]

Can each requestor have more than one email address? If not, a combobox is not needed for that, a simple textbox will do.

The first combobox should be a multi-column that includes requester ID and name, because name is not good search criteria - what if you have more than one Jane Smith? Should search on the ID.

Check out the tutorials at http://www.datapigtechnologies.com/AccessMain.htm
Look at the ones on comboboxes in section Access Forms: Control Basics.

[CJPHX]

Each person only has their one e-mail address for the company. the list of name shows their full name, so each one is unique, as it was imported form the staff heirarcy. All of this is already created. I am essentially wanting to show the email address of the user so that when the request is submitted i would liek access to auto send an email through outlook to advise them of their ticket number and any other details they might need.

[June7]

I see two ways to do this.

First combobox is multi-column with name and email address fields (email could be hidden with 0 field width). Second control ControlSource would refer to the column of the combobox that has the email address.

Or use DLookup in textbox ControlSource. Responsiveness on form can be noticeably slower than first option.

How are you sending the email - VBA code? With the multi-column combobox option don't even need to show the email in a second control. The code can just grab it from the combobox.

[CJPHX]

I hadn't worked out how I was going to send the email but yes I had planned on doing this through VBA with an on click command for the submit button of the form.

I am still new to this, so when you start discussing multi-column boxes and field widths i start to get lost. i am slowly disecting this database as I work my way through it.

[June7]

That site I referenced has good tutorials.

Here is a thread about sending email with VBA https://www.accessforums.net/import-...ook-18221.html

[CJPHX]

Thank you for the references. i was able to get it somewhat doing what I want. However it is still using a combobox. So when the user selects their name they still have to select therei email address from the a drop down but theirs is the only email in the list. Here is the code I used in the 2nd combobox. Not sure how to get it to work with a text box to just autofill.

SELECT tbl_Requestor.email FROM tbl_Requestor 
WHERE (((tbl_Requestor.RequestorName)=[forms]![frmCustomer].[cboRequestorName])) 
GROUP BY tbl_Requestor.email 
ORDER BY tbl_Requestor.email;

I did this with the query builder as the datapig site suggested I am just unable to get it to autofill without having to select it.

[June7]

I offered textbox options.

One requires the first combobox to be multi-column. ControlSource of the textbox would refer to the combobox column that has the email address: =comboboxname.Column(1). Column index starts with 0, so the name would be column 0 and email column 1.

The other uses DLookup: =DLookup("email", "tbl_Requestor", "RequestorName='" & Me.comboboxname & "'")