Forum Discussion
VBA code to check if a user is in a table
A shout to VBA Experts, I am looking for some guidance. I am a novice Access/VBA user I am self taught through forums and U-Tube.
I have made a defect record program in Access, I came across a piece of VBA code as shown below to enable or disable buttons on a Log In Form if a user’s name is recorded in a table called DMUsers and the check box for active user is ticked. This allows the user access to the main menu.
This works very well, but the problem I have is that if someone who’s user name is no in DMUsers tries to open the access program. The VBA Debugger screen appears when Access tries to load the Log In Form as shown below. I have tied various way to get around this without success. I know I need VBA to check if the StrUserName is listed in DMusers first, if then a message box if not . I would be most grateful for any guidance or help.
This Access program sits on a drive on a server,
_________________________
Private Sub Form_Load()
Dim StrUserName As String
'The Eviron method can retrieve a lot of useful information about the computer with little coding required
StrUserName = Environ("USERNAME") ' This retrieves the name of the current logged on user
' You can use this code to see if the user’s computer is on a domain or a workgroup
Dim domain As String
domain = Environ("USERDOMAIN")
' DLookup will lookup a table and return a value from the specified field
' It used as follows: DLookUp( Name of field to return, Name of table to search, Criteria)
' When using it, you should ensure that the result will return 0 or 1 record only.
' In this example, I have creaed a table called Settings and it has a single field called Domain
' In the table, I have set the name of the Domain that I am expecting this to be running on
' I am also converting value to lowercase (LCase) so it is case insensitive
'Assuming that the user is authenticated by virtue that they are on the domain and their name is in the database
'Then you can use DLookUp to set the status of buttons to restrict user functionality without needing a single password
Command27.Enabled = DLookup("ActiveUser", "DMUsers", "[UserName] Like '" & StrUserName & "'")
End Sub
A Snap of the DMUser table below
Snap of the VBA debug which appears when a user whose username is not entered in the DM Users table tries to open the program
- Environ("USERNAME") is not reliable, since it can be edited by the user. CreateObject("WScript.Network").UserName is not as easy to spoof.
- Don't use "Like" when you test for equality. Use "=".
- DCount will give a result (0), when no entry is found and a number > 0 otherwise. In VBA "=" assignment will cast to the correct type most of the time, but being explicit is not wrong. Therefore you can write:
Command27.Enabled = CBool(DCount("*", "DMUsers", "ActiveUser = True And UserName = '" & StrUserName & "'"))
2 Replies
- Environ("USERNAME") is not reliable, since it can be edited by the user. CreateObject("WScript.Network").UserName is not as easy to spoof.
- Don't use "Like" when you test for equality. Use "=".
- DCount will give a result (0), when no entry is found and a number > 0 otherwise. In VBA "=" assignment will cast to the correct type most of the time, but being explicit is not wrong. Therefore you can write:
Command27.Enabled = CBool(DCount("*", "DMUsers", "ActiveUser = True And UserName = '" & StrUserName & "'"))
Thank you so much for that guidance. it worked perfectly. I had struggled with this for a while. You comments regarding Environ duly noted .
