Access 2013 Drop Down Lists

I have a form that has two drop down lists. The first drop down is where an employee name is chosen from the employee table.

I want the second drop down to populate the employee's position title, which is in the employee table.

If the second drop down needs to be just a text box I can change that.

How do I configure this.
CMILLERAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
Use the AfterUpdate event for the Combo Box (Drop Down list).  If you only need to display the employee's position title in the second control then might as well switch to a text box control for the second.

You can include the positionTitle field in the Combo Box (Drop Down list) control along with the employee names.  You don't need to display the title in the Combo Box control however.  Then, in the After Update event for the Combo Box you could do something like
Me.txtPositionTitle = Me.cboEmployee(2)

in the example Me.txtPositionTitle refers to the text box control on the form that you want to display the position title
Me.cboEmployee(2) refers to the THIRD column (not the second; combo and list box controls are zero based) of the combo box control

OM Gang
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted as OM Gang has your solution...>
If you are creating your combobox from the wizard, ...make sure to include both these fields.
PatHartmanCommented:
Unless you are attempting to duplicate the position in a different table, the best solution is to include the employee table in the form's RecordSource query so that the text box will auto populate.  If you are not allowing the title to be changed on this form then set the Locked property to Yes to prevent accidentally changing the title.  If you want to allow updates, then the title should be a combo box rather than a text box so you can choose from the list of valid options.

Duplication data in a relational database is almost always wrong so make sure you understand why you are duplicating the data and are prepared to deal with data anomalies caused when one table is changed but not another.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

CMILLERAuthor Commented:
omgang & PatHartman

I tried to follow your suggestions but could not make it work, need more help.
omgangIT ManagerCommented:
You need to tell us what you've done so far.  Please post
1) the Row Source for the combo box
2) the name of the combo box
3) the name of the text box control on the form (where you want to populate the employee's position title)
4) what you have in the After Update event for the combo box

OM Gang
Jeffrey CoachmanMIS LiasonCommented:
<Absolutely No Points wanted>
See this sample...
it is based on the info the previous experts have given you...

Note that while you can dropdown the list and see both fields, ...only one will be displayed once you make a selection...
Database71.mdb
CMILLERAuthor Commented:
Jeffrey,

Thanks, I needed the example DB to understand.

One thing though, the Position Title when displayed once an employee is selected is not populating the position title into the DB
PatHartmanCommented:
I attached a database with several samples.  The relevant one is the fourth menu item
Auto-Populate and Cascade - 3 methods.

It shows you three techniques that can be used to populate form fields from lookups and explains why one is better than the others.
FillFormFields130127.zip

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
omgangIT ManagerCommented:
That's because the text box is unbound.  From what you describe the form should be bound to a table (name?) and the combo box is displaying data from another table (employees).  The text box is being populated with a value from the employees table during the After Update event of the combo box.  If you want that value saved to the database table (name?) the text box should be bound to the desired field in the underlying table.

All that being said you may wan to re-read Paul Hartman's comment on duplication of data.
OM Gang
CMILLERAuthor Commented:
ok, so maybe I am doing this all wrong. Let me explain this more.

I have a new form which has been designed to be a call log input form.

I have a new blank Call Log table that is the record source that stores all the call log input from the form.

For the employee name I am using a Row source from my seperate Employee query.

Row Source: SELECT [Names of Personnel].[Name Employee], [Names of Personnel].[Position Title] FROM [Names of Personnel];

Here is the Control source for the position title.
=[NameofIndividual].[column](1)
PatHartmanCommented:
Change the RecordSource query of the form to join (left join) to the [Names of Personnel] table and select the [Position Title].  Make sure to set the Locked property of the control to Yes to prevent accidental updates.
CMILLERAuthor Commented:
RecordSource query ?
PatHartmanCommented:
Yes, the Form is bound to either a table or a query.  If it is a table, create a query that joins to the lookup table.  If the RecordSource is already a query, change the query to include a join to the lookup table.  Make the join a LEFT JOIN so you will still get a record even if there is no matching lookup record.
CMILLERAuthor Commented:
Ok, I am still trying to get this to work. I will update the status soon.
PatHartmanCommented:
Did you look at the example I sent?
CMILLERAuthor Commented:
Pat,

Sorry, I missed your post with the example. Just downloaded it
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.