User Level Security, Need [CurrentUser] Tricks (1 Viewer)

I have secured my database with user level security and staff are now required to logon to access it. What I need the helpdesk system I am creating to be able to do is:

1) To be able to display the Name (not their User ID) of the "currentuser" on a form by entering an expression in the control source or some other means (I have a "tblUsers" table whereby the UserID field is the same as the User Name for users logging into Access and the table also contains a "Name" field).

2) After logging in I need for either FORM1 to pop up if the person logging in is member of ADMIN or FORM2 to pop up if the person is only a USER.

3) For a combo box on a certain form to only be enabled if the currentuser is a member of Admin.

Also, does anyone know if it is possible and if so how to get an autonumber to start from 1 again. Just a small point but it is annoying as I am doing a calls database and would like to have my list of calls received starting from 1.

Thanks for all the help I've been given on these message boards!

[This message has been edited by gyli84 (edited 08-15-2001).]

I can answer a few of your questions, but not all. (In fact, I will look forward to some of those other answers myself!)

Question #1
Type: CurrentUser() to get the name of the person that logged in.

Questions #2 and #3
I don't know how, from code, to determine a user's group affiliation. However, once you do, you can simply use a Select Case statement to open the proper form and enable or disable a combo box.

The easiest way to get an autonumber field to start back at 1 is to delete the field and then create it again. It will automatically number all of your current entries starting at 1.

As to determining group membership, the following is a possible way to do it.

Overview: A database WorkSpace includes a collection of Users. Each instance of that is one User. Each User includes a collection of Groups. One instance therein is a Group to which that user belongs.

You can do something like this...

Public Function UserHasAdmin(stUser) as Boolean

Dim wsX as Workspace
Dim usY as User
Dim grZ as Group
Dim stUName as String
Dim stGName as String
Dim boAdmin as Boolean

Set wsX = DBEngine.Workspaces(0)
Set usY as Users(stUser)
boAdmin = False
For Each grZ in usY.Groups
boAdmin = boAdmin or (grZ.Name="Admins")
Next grZ
UserHasAdmin = boAdmin
End Function

However, this isn't necessarily the right way to do it. The better way (and somewhat easier, I think) is to remember that every object has user-based and group-based permissions. Permissions added to the object for a group are available to a user if the user is a member of the group.

If you just give the Admins group full rights to both forms, but the other group only gets rights to form2, then the code that is about to open up your choice of forms has to examine the permissions of Form1 (and that's all). This is a case where a form does not have to be opened in order to get access to its innards because the overall form permissions are available in the objects in the documents collection.

So get to your Modules window, open any module, open Object Browser, and open the DAO library object. Then look at class "PermissionEnum" for the correct spellings. Write down the spellings of whatever permissions you need to use.

Now, you can do something like this:

Dim dbU as Database
Dim frmW as Form
Dim stX as String
Dim loY as Long

set dbU = CurrentDB
set stUser = CurrentUser
set frmW = dbU.Containers!Forms.Documents("Form1")
frmW.Username = stUser
loY = frmW.Permissions

If (loY And dbSecRetrieveData) <> 0 then
'bring up form 1
Else
' bring up form 2
End If

This works because when you assert the new value of the Username property, Access re-evaluates the Permissions property on the object in context of that user.

The above assumes that the less powerful users would NEVER be able to see Form1. You might wish to allow persons to open Form1 directly in read-only mode directly through some other mechanism. In that case, use another permission for your test. Such as dbSecWriteDef (the ability to update the definition of an object), which is often reserved to the Admins group, or perhaps dbSecWriteOwner or dbSecWriteSec (the abilities to change ownership or security of an object.)



[This message has been edited by The_Doc_Man (edited 08-15-2001).]

I'm not very experienced at programming in VB and am unsure as to how the code works or how to apply it. For the second set of code you say that it will be able to run automatically at startup and open the appropriate from by analysing user permissions but how should I apply it? Should I create a module containing the code, but then how do I get it to run at startup? I have tried assigning the second bit of code to see if it is bug free but when I run it it states "Invalid Use Of Property" with regards to "stUser ="
With the second piece of code does it assume that there are any objects on any forms, that any fields in certain tables are a certain name, that there are any forms of a particular name or that the database is called a certain name? Thanks for all your help so far!