Unanswered: Can I Assign a value to CurrentUser() or use golbal variables in query?
Q: (Details below)
a) Is there a way to assign a value to the control that contains the value that the function currentuser() retrieves the data from? This is a read-only area, but is there a way to get around it?
b) Is there a way to read a global variable within a query?
If either of these is not possible, my only alternative is to re-write these queries and pass the data from the forms, or reports. I would like to avoid this since there are nearly 200 queries, and over 100 forms to review. (I inherited this from someone else. I would have written much of this differently, and had half an many queries/forms…)
I have inherited a database in Access97 using native Access security. My users complain that they have to enter a password for every database they enter, and that they cannot share any databases they create with clients, since they are joined to the secure workgroup. The obvious answer is for them to join a non-secured workgroup when creating a new database, or join a non-secured workgroup, period, and add the path to the secured database workgroup to the shortcut like this:
The powers that be want me to remove the native access security, and build-in localized security. I might add, the security is only used to identify a user and point them to the proper set of menus. From those menus, their access to the data is controlled. As you can see above, this is compiled into an MDE file, so the user cannot update the code.
I have routines set up to identify the user’s system ID via API as follows:
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function Get_User_ID() As Boolean
On Error Resume Next
Dim lngTmp1 As Long, lngTmp2 As Long
gstrUserId = Space(50) 'must be preinitialized to length of largest string you may get from API,
lngTmp1 = 50 'length you made the above string
lngTmp2 = GetUserName(gstrUserId, lngTmp1)
'lngTmp2 returns the length of the UserID it retrieved + 1, API places the userid string into gstrUserId
If lngTmp2 <> 0 Then 'If lngTmp2 <> 0 then API found the UserId, If the API failed lngTmp2 would equal 0
'gstrUSerId will have a control character at the end of it, need to remove it
gstrUserId = Mid$(gstrUserId, 1, lngTmp1 - 1)
gstrUserId = UCase(gstrUserId)
Get_User_ID = True
Get_User_ID = False
My problem lies in that although I can identify the user’s system ID, and verify their password on an internal table, and read their security level, (user group), I have a LOT of code in the queries and in some form controls that includes or excludes records depending on “currentuser()”. (ie; a current user cannot verify his own vacation.)
In a non-secured database, currentuser() returns “Admin”.