User Login Question

[dvannoy]

I am currently using the system.mdw file for security. I donot like it and would like to try using something else.

does anyone out there have an example of a login form and table setup I can look at. I need to get an idea of where to start...

Thanks DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[jebry]

Hi!

I use a table with the user's name or LAN identity, a password, and a security level. The login form will have the standard two text boxes and two buttons(one is to quit Access). I use the following code in the form:

Option Compare Database
Option Explicit
Private fTries As Byte

Private Sub cmdEnter_Click()
'This procedure will check the password with the name and
'will open the main menu if they match. If no match occurs
'after three tries Access is exited

'Declare local variables
Dim dbsTestbase As DAO.Database
Dim rstUsers As DAO.Recordset
Dim UserName As String
Dim UserPassword As String

'Validate existance of information on the form
If Nz(Len(txtName), 0) = 0 Then
Call MsgBox("You must enter a name", vbOKOnly + vbInformation _
, "No Name Entered&quot
Call txtName.SetFocus
GoTo ExitSub
End If

If Nz(Len(txtPassword), 0) = 0 Then
Call MsgBox("You must enter a password", vbOKOnly + vbInformation _
, "No Password Entered&quot
Call txtPassword.SetFocus
GoTo ExitSub
End If

'Set object variables
Set dbsTestbase = CurrentDb
Set rstUsers = dbsTestbase.OpenRecordset("tblUsers", dbOpenDynaset)

'Store form information in local variables
UserName = txtName
UserPassword = txtPassword

'Test for name and password match
rstUsers.FindFirst "fldUserName = " & Quote & UserName & Quote
If rstUsers.NoMatch Then
Call MsgBox("The name you have entered is not in the official " & _
"users file. Try entering the name again. If you " & _
"are sure the name is correct, exit Access and call " & _
"the system administrator", vbInformation, "Name not Matched&quot
Call txtName.SetFocus
fTries = fTries + 1
Else
If rstUsers!fldUserPassword = UserPassword Then
Forms!frmParameters!txtSecurity = rstUsers!fldSecurity
Forms!frmParameters!txtName = UserName
DoCmd.OpenForm "frmMainMenu"
DoCmd.Close acForm, "frmPassword"
Else
Call MsgBox("The password you have entered does not match " & _
"the name you entered. Please re-enter the " & _
"password. If you are sure the password you " & _
"entered is correct, please call your system " & _
"administrator", vbInformation, "Password Incorrect&quot
Call txtPassword.SetFocus
fTries = fTries + 1
End If
End If

ExitSub:

'Test number of tries
If fTries > 3 Then
Call MsgBox("You have tried three incorrect combinations. " & _
"Access will now exit the database", vbCritical)
Call cmdExit_Click
End If

'Close object variables
Set rstUsers = Nothing
Set dbsTestbase = Nothing

End Sub

Private Sub cmdExit_Click()
'This procedure will exit Access

DoCmd.Quit

End Sub

Private Sub Form_Load()
'Initiates form level variable

fTries = 1

End Sub

As you can see, I also have a form called parameters where I store information that you might normally store in global variables. I think this method works better. This code will also exit Access after three failed tries.

hth
Jeff Bridgham

 

[dvannoy]

OK....I understand the code (i Think)..so I would need to setup a table with the following fields:

UserID
Password
Level

Now, can a user have more then one level of security?

See I have about 30 users hitting this DB and they all need different access. so your saying in the Forms Load event thats where I would specify the level???

Thanks

DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[jebry]

Hi!

All this code does is allow the user in and opens a main menu. The security level needs to be set in the user table. Each form which you open can check that security level in the Load event and/or the Open event to either open or not open the form, or restrict access to the information depending on the security level. For instance, in the Open event, you could use:

Select Case Forms!frmParameter!txtSecurity
Case 1, 2, 4, 5, 6
Cancel = -1
End Select

Now anyone with a security level of 1, 2 or 4-6 will not be able to open the form. In the form's Load event you could put:

Select Case Forms!frmParameter!txtSecurity
Case 3
Me.AllowAdditions = False
Me.AllowEdits = False
Case 7, 8, 10
Me!txtName.Locked = True
Me.AllowAdditions = False
etc.
End Select

I'll admit that 30 security levels will require a lot of coding, but you can hopefully do some combining like that above on each form. If you go through a main menu form like my original code does, then that is a good place to restrict the users activities simply by making the buttons they won't need to use invisible.

I know this is a lot of information all at once, but I hope it helps.

Jeff Bridgham