Access: VBA to identify User and rights

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,678
I have code that checks a table to see if the Current User should be able to open a form

Code:
Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Error_Handler
 
    If Nz(DLookup("UserName", "tbl_Users", _
                  "[Object_Name]='" & Me.Name & "' AND [UserName]='" & CurrentUser() & "'"), "") = "" Then
        Cancel = True
        MsgBox "You are not authorized to Edit/Modify Commodities"
    End If
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
What I want to do is modify this code so that it checks the table, like its doing, but now I want it to populate an unbound filed on the form so that it reads the user name and if they have rights.

So if it finds the user name and the form name the unbound text field (Text293) would = "Joe.Smith has Modify rights"
assuming the users name was Joe.Smith
Else it would say "Joe.Smith Does not have Modify rights"

Is this possible?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,508
You could just put the formula in the unbound text field and wrap it with IIF:

Code:
=IIF(
	Nz(DLookup("UserName", "tbl_Users", "[Object_Name]='" & Me.Name & "' AND [UserName]='" & CurrentUser() & "'"), "") = "",<br> 
        Me.Name & " has Modify rights",<br>
	Me.Name & " does not have modify rights."<br>
)
I left the newlines in the above so you could see the structure of the IIF() formula -- first the expression, then the output if true, then the output if false. In reality you wouldn't keep the newlines in the formula.
 
Last edited:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,508
Also I guess you could just change your open event code (not sure why you found this hard):

Code:
Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Error_Handler
 
    If Nz(DLookup("UserName", "tbl_Users", _
                  "[Object_Name]='" & Me.Name & "' AND [UserName]='" & CurrentUser() & "'"), "") = "" Then
        Me.Text293 = Me.Name & " has modify rights."
    Else
        Me.Text293 = Me.Name & " does not have modify rights."
    End If
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
If none of this is working I think I'd have to know more about Me.Name in the above code (it seems you are looking for a name to see if the user has rights, so I assume you have the name at the time you are opening the form - maybe it is a bound field at runtime or something like that).
 
Last edited:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
677
@xenou
Me.Name is the name of the form?
@gheyman
You would need to store the name of the user with their login name and retrieve that for that particular user in someway, possibly another DLookup

I would probably do it once on login and store in Global variables or TempVars
 

Forum statistics

Threads
1,077,738
Messages
5,335,921
Members
399,057
Latest member
mgabr

Some videos you may like

This Week's Hot Topics

Top