Limit Selections in Combo Box to Certain Users

[wily_wolf]

Hello,

I am working with Access 2013 and would like to limit the selections in a combo box depending who the user is using the database. For example, I have 5 choices in a combo box: Technical, Operational, Capability, Oversight, and Coaching. My goal is to make "Coaching" selectable for only a certain group of people. Just FYI, the form which contains the combo box already recognizes who the user is by using the Environ(Username) code to grab the username.

Thanks in advance!

[RuralGuy]

What is the Row Source of the Combo Box and the Row Source Type?

[wily_wolf]

The Row Source Type is a Table/Query, and the Row Source is pulling the "ID" and "Buckets" from tblBuckets and displaying the names of the certain buckets (i.e. Technical, Operational, Capability, Oversight, and Coaching)

[RuralGuy]

Is there a field in the tblBuckets whose field would allow you to limit the appropriate selections?

[RuralGuy]

What I'm leading up to is another query that would present the appropriate select in the ComboBox. You could then switch the Row Source in the OnLoad event of the form or mayte even the OnEnter event ofn the ComboBox. What you want to avoid if possible is *hard* coding the group of users into the ComboBox events.

[Micron]

Could be as simple as adding a level or sort field to the table and modifying the query to show everything < that value, which is in a hidden form text box, for example. Or the query could call a function which does the same (preceded by an operator such as < but I'm not sure if the operator and returned function value are compatible). One could use a Select Case block to run the appropriate qry or sql statement on form open. Or the aforementioned level could be linked to the user table. Many ways to skin this cat. For what it's worth, I use fosUserName, not the environ value.

[wily_wolf]

What if I created a table that contains each of my employees, their ID #'s, and each category they are associated with? There would be multiple records for each employee but, there must be some code that'll work easier with that.

[RuralGuy]

I'm not sure to whom you are talking but that new table to which you refer would probably work just fine in a query that limited the selection to the current employee. It could refer to your form and be the RowSource of your ComboBox.

[wily_wolf]

I normally don't create tables containing repetitive information but there aren't that many employees so I just thought I'd throw the idea out there to whomever. Would I make the query pull the user information, or do it in VBA?

[RuralGuy]

Let the query do the work.

[Micron]

I normally don't create tables containing repetitive information but there aren't that many employees so I just thought I'd throw the idea out there to whomever. Would I make the query pull the user information, or do it in VBA?

Sometimes it is inevitable, and it's still just a one-to-many relationship that happens all the time. That does not mean the data is not normalized, just that in some cases, it can't be normalized further. I guess I've been lucky enough so far to have been able to sort permissions in a logical fashion, give them sort order id's and just coded to allow access to anything less than 3 for one type, less than 5 for another, etc.