Avatar of Joanne Bailey
Joanne Bailey
Flag for Canada asked on

Access 2010 combo box

I've inherited a rudimentary database which many issues. One of the things that is bothering me is that I am working with a CURRENT STATUS combobox lookup comprised of First Names of some of the staff who work with clients plus the value "Closed". So when there are staffing changes, the Current Status table values have to be updated and currently do not link to the employee table. I would like to populate this lookup from the Employee table but how would I do that and also include the "Closed" value in the cbobox? This is not something I've had to do before
Microsoft Access

Avatar of undefined
Last Comment
Fabrice Lambert

8/22/2022 - Mon
John Tsioumpris

So the solution would be to link the Employee table to the Current Status and checkmodify the combobox accordingly...It would be most helpful if you share a sample of this database..
Joanne Bailey

ASKER
Hi John. I am trying to get away from having a separate Current Status table since I think the values should come from tblEmployee where CurrentStatus (Y/N) permission = True. I recall there is a way to do this using a union query but have not used union queries before. I can't attach the database (due to confidentiality agreeement) but I can tell you that I am simply trying to populate a combo box on a form which is bound to the CurrentStatus field in tblCase with First Names from tblEmployee where CurrentStatus is True and also offer the value "Closed" in the pick-list, Hope that makes sense.
Jeffrey Coachman

Then post a simplified example of the database with generic data, ...just to illustrate the issue..

Remember, without a full understanding of the design, ...we are limited to what we can propose as an "answer"

For example,
...it is no clear how a status of "closed" relates to the employee and the clients,...and under what conditions is it "Closed" or "Open"?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Fabrice Lambert

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Joanne Bailey

ASKER
Thank you Fabrice, that is exactly what I was looking for. Seems so simple now. Also understand and agree with Pat but "Current Status" is not an accurate label/field name. This field is used to show who is looking after the file now. And the client wants "Closed" to appear in the list. Thank you everyone for your assistance.
SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Fabrice Lambert

Solution accepted, but the original poster did not gave credits