Access: VBA to identify User and rights

[gheyman]

I have code that checks a table to see if the Current User should be able to open a form

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]

You could just put the formula in the unbound text field and wrap it with IIF:

=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.

 

[xenou]

Also I guess you could just change your open event code (not sure why you found this hard):

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).

 

[welshgasman]

@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