Limit values of a Combo Box based on username?

[thiazi]

I have a table in which I have a list of names who are assigned to certain categories. Is it possible to limit the values that appear in a Combo Box based on the username of the user accessing the database? (So essentially, I'll have to create a separate table I guess that defines which usernames have access to which categories.)

I've done a bit of research and found that environ("username"), while it can be circumvented, identifies the username and can be used in the code. For this application, that's more than sufficient.

So would I create a separate table associating usernames with the categories, link the two together, and then the code would be an if/then situation? If Environ("Username") = "thisusername" Then?

 

[thiazi]

Would creating a table with usernames and category levels, then putting an environ("username") call in the onload referencing the table data work? I'm not sure how I'd code that, though.

 

[Wiz47]

Would creating a table with usernames and category levels, then putting an environ("username") call in the onload referencing the table data work? I'm not sure how I'd code that, though.

Yes, you could create separate data tables for the combobox and use the Onload or OnOpen event to populate it based on the Username/level.

Something like:

If User.AccessID = 1 Then
    
     Forms![frmYourForm]![frmCombo].RecordSource = "tblOne"

Elseif User.AccessID = 2 Then

     Forms![frmYourForm]![frmCombo].RecordSource = "tblTwo"

Elseif User.AccessID = 3 Then

    Forms![frmYourForm]![frmCombo].RecordSource = "tblThree"

End if

Another method would be to create separate comboboxes based on the user level, then only make it visible based on the username/level of the username in the main table.

 

[thiazi]

Would you mind you elaborating on that just a bit more? I'll create a data table that will contain the usernames, I presume called AccessID, and then another column with the categories?

I could layer the combo boxes on top of each other and only set one to be visible, I'm just not sure on the code that would be needed.

 

[Wiz47]

Would you mind you elaborating on that just a bit more? I'll create a data table that will contain the usernames, I presume called AccessID, and then another column with the categories?

I could layer the combo boxes on top of each other and only set one to be visible, I'm just not sure on the code that would be needed.

The level of access is AccessID. (but you could call that field anything you want) Then have a simple login screen to validate the login user. Assign that login name to a public variable (I called it USER) and use it as a match against the table. When the user activates that form, it will load the combobox based on that login info using the code from my earlier reply.

If you want to layer the comboboxes, set all the comboboxes to not visible in the properties, then change the code to ...

If User.AccessID = 1 Then

Me.ComboName1.Visible = True

Elseif User.AccessID = 2 Then

Me.ComboName2.Visible = True

Etc ...

If you're still a bit hazy on this, upload a small version of your db and we'll work on it to get it to do what you want.

 

[thiazi]

OK, I get it. How could I incorporate Environ("Username") to get the username automatically so the user doesn't have to type anything?

 

[Wiz47]

OK, I get it. How could I incorporate Environ("Username") to get the username automatically so the user doesn't have to type anything?

As long as you had a table that had all the possible login names for environ (UserName), then the method would be the same. You would use the Environ(UserName) and assign it to the USER public variable. That would then be matched to the Name and AccessID level in the table. Because the program doesn't automatically know the Access level for every user, you would need a table to match against.

This also means you have to have an error handler if there is no match.