Restrict the values in a combo

[CW]

When we log new inquiries on our Orders form, we allocate a salesperson to
each one. We use a combo that looks up the Employees table and any name can
be selected. Trouble is, only a few of them are actually salespeople and
sometimes a non-salesperson is selected in error.
If I flag the employees that are salespeople (perhaps a yes/no field in the
Employees table??), how can I then restrict the persons shown in the combo to
just those?
Many thanks!
CW

 

[Jeff Boyce]

Before you simply add a "flag" to your employees table, will you ever need
to track what role an employee played at different points in time? What was
John Doe doing before he became a salesperson? What is he doing now, that
he's no longer a salesperson?

If you will only ever need to know what folks are doing right now, you still
may not wish to add a simple yes/no (flag) field. What happens when someone
wants info about all persons who are managers? Who are "deliver
drivers"?...

I suspect you'll be needing a couple new tables, one for Roles, and one for
EmployeeRoles.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Beetle]

Adding a yes/no field to your employees table would work as long as an
employee can only be one of two things (either a salesperson or not a
salesperson). If you decide to do it this way, then you would add that same
field to your combo box row source query, and put "True" in the criteria row
for that field in the query design grid.

If you have more than two types of employees this would not be a good
solution. In that case you woul need a separate table for employee categories
(or something similar).

HTH

 

[CW]

Jeff -
That's a very good point. As a matter of fact, thinking about it, I could
usefully identify some other folk as Coordinators, so I will definitely add
those extra tables as you suggest.
Back to my core question - how would I get the combo to display only certain
of those persons?
Very grateful for your help
CW

 

[Jeff Boyce]

Base your combobox on a query. In the query, set the selection criterion to
the role(s) you wish displayed. The query will join the person and role
tables...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[CW]

Ah yes, of course - why didn't I think of that
Thanks again Jeff
CW