Solved Check If Logged User is in User Table

[kengooch]

When someone accesses the DB, it checks their user name against a list in my VBA Code using Select Case. I am trying to move that list of users to a table that I can then add and remove users without having to change the code each time. I created a table "tUsrAth" with one field [UsrAthID]. I obtain the user name as vUser = Environ("UserName"). I tried using DLookup to lookup vUser in tUsrAth thinking that I could use an If statement and if the find is true, then allow them access if not, end the program.
Here is the code that I currently have that works. My Goal is to replace the select case with some sort of lookup that uses the tUsrAth table.

Sub mCheckUser()
AuthorizedUser = True
vUser = Environ("UserName")
vComputerName = Environ("ComputerName")
' vUser = InputBox("Enter invalid user name", "Test by entering an invalid User Name")
Select Case vUser
Case "vhamwvgxxxhk":
Case "vhamwvrxxxmt":
Case "vhamwvaxxxbc":
Case "vhamwvaxxxla":
Case "vhamwvbxxxes":
Case "vhamwvhxxxim":
Case "vhamwvhxxxic":
Case "vhamwvjxxxxsk":
Case "vhamwvtesxxxb":
Case "vhamwvshxxxa":
Case "vhamwvstexxxs":
Case Else: AuthorizedUser = False
End Select
If AuthorizedUser = True Then
' MsgBox "Welcome your user name [" & vUser & "] has been validated as an authorized user of the VaxTrack Database. " & vbCrLf + vbCrLf & "You are currently logged on to VA Computer " & vComputerName & ".", vbInformation, "VaxTrack User Authorization"
Else
MsgBox " ! ! ! ! ! W A R N I N G ! ! ! ! !" & vbCrLf + vbCrLf & "[ " & vUser & " ] is not authorized to access this database and you are currently trying to access this Database from VA Computer " & vComputerName & ". This access attempt has been recorded for security purposes. If you attempted to access this by mistake or if you need access to this database please contact" & vbCrLf + vbCrLf & "For Access contact:" & vbCrLf & "" )", vbCritical
DoCmd.Quit
End If
End Sub

Any help would be GREATLY appreciated.

 

[Gasman]

I'd use a DCount().
If zero, then user is not in table.?

 

[theDBguy]

Hi. You should be able to simply use the DCount() function. For example:

If DCount("*","tUsrAth","UsrAthID='" & Environ("Username") & "'")>0 Then

Hope that helps...

Edit: Oops, too slow...

 

[arnelgp]

you should also be aware that if the db is FE/BE environment,
there is possibility that another computer might have same "username"?

 

[Gasman]

Ken,
You might want to edit your post and remove/amend the email addresses as well?

 

[kengooch]

Wow!! Thanks to all who helped! And I do appreciate the recommendation to remove names and extensions. You guys are the best!!

 

[jdraw]

ken,
You may get some ideas and insight from this Steve Bishop video.