Microsoft Access
Microsoft Access

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

Joanne BaileyDatabase AdministratorAsked: 2017-11-15

Who is Participating?

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

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.

LVL 24

John TsioumprisSoftware & Systems EngineerCommented: 2017-11-15

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 BaileyDatabase AdministratorAuthor Commented: 2017-11-15

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.

LVL 74

Jeffrey CoachmanMIS LiasonCommented: 2017-11-15

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"?

LVL 14

Fabrice LambertFabrice LambertCommented: 2017-11-15

Hi,
try with the following query in the combo box's rowSource:

SELECT [First Names]
FROM tblEmployee
WHERE CurrentStatus = True
GROUP BY [First Names]
UNION
SELECT DISTINCT "Closed"
FROM tblEmployee

Open in new window

In fact, the table in the 2nd query doesn't matter since you arn't pulling data from it, but Access require it (duh !).
This is why the DISTINCT statement is required, to avoid duplicate results.

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

LVL 43

PatHartmanCommented: 2017-11-15

It would appear that you have a design issue. You are using one field to hold two attributes Open/Closed and a person's name. It would be better to fix the underlying problem and use two fields. Your status values, assuming they are pretty static, can be a value list. Then the second combo can pick an employee.

Joanne BaileyDatabase AdministratorAuthor Commented: 2017-11-16

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.

LVL 43

PatHartmanCommented: 2017-11-16

Yes, you can add "Closed" to the list as Fabrice suggested but it prevents you from using RI. It would be better to add "Closed" as an employee. But using the field for two purposes is still WRONG and that is what should be fixed.