User Level Security via VBA (Environ)

[Joker via AccessMonster.com]

Hello,

I have set up thru VBA my own security but I'm running into a little problem
with a update I'm trying to make. Previously I had it opening one form if
your authorized to go into the database and had the correct password but now
I'm going to need to have a case statement (or something similar) depending
the the users security level if they get the read only form or if they get
they data entry form. Any ideas on what I can do to correct this would be
greatly appreciated. Below is my code that works.

Private Sub cmdLogin_Click()

Dim rsUser2 As Recordset

'authenticate user, obtain access level and department


On Error GoTo cmdLogin_Click_Error

If AuthenticateUser <> True Then
MsgBox "Unable to log in. Please try again."
Me![cboUser] = Null
Me![txtPassword] = Null
Me![cboUser].SetFocus
Else
Call DetermineAccess


'---------------------------------------------------------
' THIS DECIDES WHAT FORM YOU'RE ABLE TO VIEW
'---------------------------------------------------------

DoCmd.OpenForm "frm_Main", acNormal

Me.Visible = False
End If

On Error GoTo 0
Exit Sub

cmdLogin_Click_Error:

End Sub

~~~~~~~This is my most recent update trial~~~~~~~
Private Sub cmdLogin_Click()

Dim rsUser2 As Recordset
Dim strSql2 As String
'authenticate user, obtain access level and department


On Error GoTo cmdLogin_Click_Error

If AuthenticateUser <> True Then
MsgBox "Unable to log in. Please try again."
Me![cboUser] = Null
Me![txtPassword] = Null
Me![cboUser].SetFocus
Else
Call DetermineAccess


'---------------------------------------------------------
' THIS DECIDES WHAT FORM YOU'RE ABLE TO VIEW
'---------------------------------------------------------


strSql2 = ""
strSql2 = strSql2 & "SELECT Analyst.* FROM Analyst "
strSql2 = strSql2 & "Where Analyst.GID ='" & Environ("username") & "';"


Set rsUser2 = CurrentDb.OpenRecordset(strSql2)

If rsUser2.BOF = True And rsUser2.EOF = True Then
'user not found in table... close the database
Set rsUser2 = Nothing

Select Case rsUser2.Fields("User_Access_Level").Value
Case Is = 1
DoCmd.OpenForm "frm_Main", acNormal
Case Is = 2
DoCmd.OpenForm "frm_Main", acNormal
Case Is = 3
DoCmd.OpenForm "frm_MainReadOnly", acNormal
End Select
Me.Visible = False
End If
' DoCmd.OpenForm "frm_Main", acNormal
' Me.Visible = False
End If
On Error GoTo 0
Exit Sub
Form_Load_Exit:

Exit Sub
cmdLogin_Click_Error:

Errorhandler:

MsgBox Err.Number & ", " & Err.Description & ", frmUser_Login.Form_Load."

Resume Form_Load_Exit
End Sub

 

[Joker via AccessMonster.com]

Does anyone have thoughts or suggestions on this issue? I'm really stuck on
this problem.

 

[RD]

Hi Joker,

I know it's been a while but if you're still watching this thread ...

First, I roll my own security very often. You must realize that rolling your
own is not really secure. That being said, here are a few things to think
about:

* Password protect the database; don't store the password anywhere in the
database (not in tables or forms ... anywhere)
* Set up a table with the users windows login and their permission level; set
the table attribute to Hidden
* Get the user's login ID using the API:
http://www.mvps.org/access/api/api0008.htm
* With a simple mix of SQL and Case statements you should be able to get what
you're after.

* Utter air code:
sLogIn = fOSUserName() ' from MVP API function above
strSQL = "SELECT Permission FROM tblUser WHERE LogIn = '" & sLogIn & "'"
rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

Select Case Permission
Case "Level 1"
Some code
Case "Level 2"
Some other code
etc.

Hope that gives you something to chew on,
RD