Access: VBA to identify User and rights

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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:
Upvote 0
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:
Upvote 0
@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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top