CREATING A LOGIN SYSTEM FOR A HELPDESK PROGRAM 1

[gyli84]

I am creating a Helpdesk system in Access and I would like to create a simple login system. I would have a login table with the Fields "USER ID", "PASSWORD", "ACCESS LEVEL" and "STAFF ID". When someone enters the database I would like a form to pop up which would request the user to enter their ID and password. What I need the login system to be able to do is:

1)Check that the user ID and password match from the login table
2)Direct the user to a form dependent upon their access level (there will be a form for helpdesk users and a unique one for the helpdesk manager ie. 2 acess levels)
3)Store the user ID the user logged in with in a temporary table (known as a control table in Delphi) whilst they are logged in such that for example an object on the form could display the logged in user's name (a staff ID in the login table allows a relationship between it and a STAFF table to be made).

I'm sure that this would require VBA programming of some sort but am a novice when it comes to VBA!

 

[jebry]

Hi!

This code may help:

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
Select Case rstUsers!fldAccessLevel
Case "User"
'Open users form'
Case "Manager"
'Open managers form'
End Select
frmUserID!txtUserID = UserName
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


Note that this code stores the user id in a textbox on a form called frmUserID. I prefer this method rather than accessing a temp table all of the time or using global variables. I usually call this my parameter form and I use the autoexec macro to open the form and hide it.

hth
Jeff Bridgham

 

[gyli84]

Thanks for posting the VB script up! Some people have suggested that it might be a good idea to create a login system just by making use of the workgroups and user level security function in Access. Personally I have never tried workgroups but do you know what the disadvantage of it would be compared to say what this script can achieve. I don't think it will be possible to assign a separate form dependent upon the user access level or place the user id textbox on frmUserID if Access User Level Security were used. However, by using this code would it still be possible to secure the database using Acess User Level Security workgroups so that certain users only had access to certain forms (wouldn't this login script interfere with it or would the user have to log in twice?). Anyway, in reading the code and trying to understand it I have a few questions seeing as I am a VB novice:

1) Do I merely just save this code as a function within Access or does it have to be inserted into a Form to run on a ceertain event? Does it specify anywhere in the code what this Login Form must be called?

2) Wherever it says dbsTestbase, do I replace dbsTestbase with the name of my database?

3) Where you say "'Validate existence of information" on the form what does "Nz" mean and does the textbox on my login form have to be called "txtName". How does the script know what my login form is called?

4) If I am correct the login table this script will refer to will be called "tblUsers" and it will contain the fields UserName, UserPassword and AccessLevel

5) Where you have the comments "Open Users Form" and "Open Managers Form" do I need a command inserted to open whatever the form for the user or for the manager is called and what would be that command?

6) Do I have to create a form called frmUserID with a textbox txtUserID? Is there such a thing as an autoexec macro in Access?

Thanks, you've been a great help, by the way, my e-mail is garyli@weconnor.com

 

[gyli84]

Ok, I have figured some things out for myself but when the code is initiated by cmdEnter_Click() I get the following error message. Run Time Error 3070, The Jet Database Engine does not recognise 'fldUserName' as a valid field name or expression with regards to the "Testing for name and password match" section of the code. I have a table called "tblUsers" with the fields, "UserName", "UserPassword" and "AccessLevel". What exactly does the fld prefix before UserName mean?

Also, I still can't figure out question 5 I posed above about opening the user's form and manager's form.

Thanks for all the help