Removing bound property of combo box for certain users

[CompostKid]

Hi, I've got a timesheet database with user level security set up. To stop the multiple users being able to see other peoples records within the database, I've bound the control source of the name combo box to =CurrentUser().



This is where the question comes. For some users (the managers) I don't want the box to be bound so they can select other users for review purposes. I think I need an If statement in here but am a complete programming novice.

Does anyone know what code (if its possible) I should put behind the control to make this possible.....

Many thanks

Dave

[Rawb]

Create a copy of the Combo Box that's not bound. Then, On Form Load, just switch the visibility of the two Combo Boxes based on the User's Group. . .

The other option is to actually have two separate Forms: One where the Combo Box is locked to the user and one where it isn't. Then, on Form Open, you can have an IF statement that switches Forms (closes one and opens the other) based on the User's Group.

If you're using UAC, here's a simple Function to check if a certain user is a member of a certain Group:

Private Function MemberOf(strGroupName As String, strUserName As String) As Boolean
  Dim user As DAO.user
  Dim work As DAO.Workspace

  On Error Resume Next

  Set work = DBEngine.Workspaces(0)
  Set user = work.Groups(strGroupName).Users(strUserName)

  MemberOf = Err.Number <> 3265
End Function

With the above function, the following Code would return True if the user Rawb was a member of the Admins Group:

MemberOf("Admins", "Rawb")

[CompostKid]

Hi Rawb,

Thanks for your help on this. I've put the code into the "on Open" event for the form however it keeps coming up with the error message "Ambiguous Name Detected: MemberOf"

Any ideas?

Many thanks,

Dave

[Rawb]

The "Ambiguous Name Detected" error means that you're declaring the same variable (in this case MemberOf) in two different places. Because of this, Access doesn't know which MemberOf variable you're trying to use.

Most likely, one instance of the variable is declared Publically via a Module (or at the top of your Form's VBA Code), and another one was declared inside your Function. However, it's also possible that both instances are Publically declared variables from a Module, etc. (this happened to me once!).

[CompostKid]

Thanks Rawb, I've had a play around with it and I think the easiest way will be to create two slightly different front ends - one with the box locked for the emplyees and one unlocked for the managers.

Now for the joys of splitting....

Thanks for your help

Dave