Using a table in an "if" statement 1

[lisaharris]

I'm using an IF statement to verify the windows system user that is clicking a button on a form in Access. The list is going to get lengthy and would be easier to maintain in a table within the database rather than hard-code into the VBA each time there's a change. I've already created a table called WaiverAuthority with one field called Supv containing the usernames that can click this button.

Any help with recoding that IF statement below will be GREATLY appreciated!!

Private Sub Command1_Click()
Dim UserNameVBA As String
UserNameVBA = VBA.Interaction.Environ("USERNAME")

If UserNameVBA = "lhadmin" Or UserNameVBA = "jscarpa" Or UserNameVBA = "wholling" Or UserNameVBA = "cscott" Then GoTo YouPass Else: GoTo NoPass

NoPass:
    MsgBox ("User " & UserNameVBA & " does not have access to this function.")
Exit Sub

YouPass:
Application.DoCmd.OpenForm "Waiver Screen"
Exit Sub
End Sub

 

[Remou]

You can use DlookUp.

Environ("UserName") does not work in all environments.

 

[chandlm]

You can use this method to get the currently logged in username

Set wshnetwork = CreateObject("WScript.Network")
MsgBox (wshnetwork.UserName)

Just substitue msgbox to whatever variable you want to store the username in.

Matt

 

[lisaharris]

Got it using the dlookup... Environ("Username") is working fine on my network, it stays for now... here's the code I used in the module:

Private Sub AdminButton_Click()

Dim UserNameVBA As String, WaiveAuth As Variant

UserNameVBA = VBA.Interaction.Environ("USERNAME")
WaiveAuth = DLookup("[Supv]", "WaiverAuthority", "[loggeduser]=[Supv]")

If WaiveAuth = UserNameVBA Then GoTo YouPass Else: GoTo NoPass

NoPass:
    MsgBox ("User " & UserNameVBA & " does not have authority to waive the Fee. ")
Exit Sub

YouPass:
Application.DoCmd.RunMacro "Case4AdminWaiver"
Exit Sub
   
End Sub