CODEWITHSUNDEEP
vbams-access
Tiny1127
Tiny1127

Reputation: 37

Using System User Name to grant permission

I have a global function that gets the current users ID. It works file. Now I want to compare that to a list of users in a table that I want to have access to the program. My table is Tbl_Staff The field that holds that values for each person is UserID My Function is called GetUserName Here is what I have so far:

Private Sub Form_Load()

Dim Message         As String
Dim Currentdb       As DAO.Database
Dim RecSet          As DAO.Recordset

Set RecSet = Currentdb.OpenRecordset("SELECT * FROM Tbl_Staff WHERE [Tbl_Staff].[UserID] = UserName())

If UserName = RecSet Then

Exit Sub

    Else

        Message = MsgBox("You do not have permission to access this database." & vbCrLf & "Please contact Robert Smith for access", vbOKOnly)

    DoCmd.CloseDatabase
    End If

End Sub

I am really stuck. Thank you

Upvotes: 1

Views: 125

Answers (2)

Santosh
Santosh

Reputation: 12353

Another way, use the access built-in function

Private Sub Form_Load()

    Dim blnUserExist As Boolean
    blnUserExist = Nz(DCount("*", "Tbl_Staff", "UserID='" & Environ("username") & "'"),0)

    If blnUserExist = False Then
        MsgBox "You do not have permission to access this database." & vbCrLf & "Please contact Robert Smith for access", vbOKOnly
        DoCmd.Quit
    End If


End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166316

Something like this:

Private Sub Form_Load()
    Dim RecSet As DAO.Recordset

    Set RecSet = Application.Currentdb.OpenRecordset( _
       "SELECT * FROM Tbl_Staff WHERE [Tbl_Staff].[UserID] = '" & UserName() & "'")

    If RecSet.EOF Then
        MsgBox "You do not have permission to access this database." & _
                vbCrLf & "Please contact Robert Smith for access"

        DoCmd.CloseDatabase
    End If

End Sub

Might need to tweak the SQL to account for differences in case between the username in the DB and the return value from UserName()

Upvotes: 1

Related Questions