Help with allowing administrator access using user name

[BiotechJen]

I feel very dumb, because I set up this database a long time ago when I was using Access more frequently and more familiar with programming (although I have never been anything close to an expert). Now I have forgotten a lot and need to make a change to my database - hoping someone will be able to suggest how best to go about it!

I have a database on a network drive, but accessed by only a few people. For various reasons, it was decided not to apply Access security, but to control who can access administrator portions of the database based on their Windows user name. This has always worked well. It was set up with a simple system where there are three tables, each containing just one name, and those three people were the only ones who had access. I could easily change the name in any table to change who had access.



Of course, inevitably , now I need to allow access to a larger group of people. I'd like to keep the three tables, but be able to enter more than one name in each of them. But all of my "access control" screens are set up using DLookup to check whether the current user matches any of the three names. I am stuck on how to do this if the tables have multiple records, although I think (hope!) it should still be doable. I'm guessing I have to loop through the records rather than just read them, but my limited memory of VBA doesn't recall how to do this!

Here is my current code. Note that similar code exists throughout the database where it's required to confirm that someone has access before proceeding. And yes, I acknowledge that this code is very rudimentary - please be kind! (The length 8 thing is just because our Windows user names can be long, but in access I cap the user names at 8 characters, so this allows me to match them.)

Thanks in advance for any suggestions!
Jen

Private Sub Command8_Click()
    Dim CurrReception As String
    Dim CurrAdmin As String
    Dim CurrHR As String
        
    'The code below pulls the name of the person currently using the database and compares it to the currently assigned Reception, HR, and Administrator to see
    'whether the user has permission to access the Admin portion of the database.
    
    'CurrReception = DLookup("CurrentReception", "CurrentReception")
    'CurrAdmin = DLookup("CurrentAdmin", "CurrentAdmin")
    'CurrHR = DLookup("CurrentHR", "CurrentHR")
    
    
    'Get current user's full name.
    'Dim MyName As String
    'MyName = Environ("UserName")
    'If Len(MyName) > 8 Then
        'MyName = Left(MyName, 8)
    'End If
    'Dim MyFullName As String
    'MyFullName = DLookup("FullName", "EmployeeFullNames", "EmplCode = '" & MyName & "'")
    
    'Check if the user has privileges to enter the admin area.
    'Dim Response As Integer
    'If MyFullName <> CurrReception Then
        'If MyFullName <> CurrAdmin Then
            'If MyFullName <> CurrHR Then
                'Response = MsgBox("You do not have permission to view the Administrator options.", vbOKOnly + vbExclamation, "No Permission")
                'Exit Sub
            'End If
        'End If
    'End If
    
    DoCmd.OpenForm "Switchboard - Admin"

End Sub

[Robeen]

Quick question:
Could you not put all your names in one Table?

You can add a Field to the Table to specify whether the person is Reception, HR or Administrator.
Then - instead of getting the values from your three tables like this:
'CurrReception = DLookup("CurrentReception", "CurrentReception")
'CurrAdmin = DLookup("CurrentAdmin", "CurrentAdmin")
'CurrHR = DLookup("CurrentHR", "CurrentHR")
. . .
you can just check if:
'MyName = Environ("UserName")
. . . matches ANY of the names in the one Table that contains all your names.

Would that work for you?
Seems [from what you've described] that you shouldn't need three tables to hold one name each. One Table should suffice.
Plus - having three tables for three names is not making good use of database design principles.

Try putting all your names in one Table. This should not be a difficult change.

Let us know if you have questions.

[BiotechJen]

Thanks for your reply! Yes, you're right - they definitely could all be in one table. And I think I can handle the code to get them into the table in the first place. This is probably a good time to make that change, since I'm having to change it anyway.

So what code would I use for the next step after 'MyName = Environ("UserName")...how do I scan through the records in my table to see whether MyName is in there? I have lots of experience looking up specific records from a table, but I'm not sure how to look for ANY record.

Thanks again,
Jen

[Robeen]

You can try using the DCOUNT() function to tell you if there are any matches.
Try something like this . . .

Dim strName as String

strName = Environ("UserName")

If DCount("FieldName", "TableName", "UserNameField = '" & strName & "'") > 0 Then
     'User Exists . . . allow.
Else
     'User Does Not Exist . . . do not allow.     
End If

You can also use DLOOKUP(). I believe the DLOOKUP() function returns a Null if the searched value is not found in the table.
so you can do something like . . . If DLOOKUP( . . . .) Is Not Null Then . . . .

Hope this helps!!

[BiotechJen]

I think I have it working now - thanks so much for your help!