Restrict contents of combobox?

[jonathanchye]

I currently have a combobox which displays 8 options. These options would be used to update the results in my listbox.

I am wondering if it is possible to control what the combobox displays depending on users. For example for user 1 I want to display options 1,6 and 9. For user 2 I want to display all options, for user 3 I want to display only option 1 and so on.

Is this possible? If it is not possible using combobox can anyone suggest a better way of doing this?

Thank you.

 

[boblarson]

I would use a table for keeping this. Have a table like this:

tblUsersOptions
UserOptionID - autonumber (PK)
UserID
OptionNo

and then user one would have 3 rows for 1, 6, and 9, etc.

Then you should be able to link that table with your other table based on options to have it show with the UserID as criteria from the tblUserOptions table.

 

[jonathanchye]

Hmmm, good idea but wondering if it will work in my case?

What I have now is actually a drop down box showing 8 departments. A query will be run according to the selection from this combobox.

It works well now as I can lock the combobox and select the value of the combobox to a department according to users. So user John only has access to Accounts for example. All I do is this simple code :

1) use dlookup so find what department the said user has access to
2) use the Form's On Load event and apply this code

Select Case AccessDepts
Case "Accounts"
Me.comboboxDept.Value = "Accounts"
Me.comboboxDept.Locked = True
etc....

However, the client wants 1 user to have access to multiple departments. Now this gets a bit trickly. Just wondering if there is any clever way around this...

 

[gemma-the-husky]

partial quote

However, the client wants 1 user to have access to multiple departments. Now this gets a bit trickly. Just wondering if there is any clever way around this...

which there is, and bob has just explained it to you ....