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
Code:
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