Login System Coding Problems (1 Viewer)

[gyli84]

I am creating a login system for a helpdesk database and was advised that this following code would 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")
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")
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")
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")
Call txtPassword.SetFocus
fTries = fTries + 1
End If
End If

ExitSub:

I have a table called "tblUsers" with the fields "UserName", "UserPassword" and "AccessLevel". I also have a login form with "txtName" and "txtPassword" fields and a "cmdEnter" and "cmdExit" button. The problem is that when i run the code I get a 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. Does anyone know what I am doing wrong, how to resolve this or what the fld prefix actually means?
I also required the database to open a different form dependent upon whether the "AccessLevel" of the user was "Manager" or "User". Where it says Case "User" in the code do I have to add a line to open the relevant form and if so how?

Thanks in advance!

 

[axa]

gyli84

here is an alternative version of the code below. you may find it easier to understand than the code you posted, and it should work as you want.

__________________________________________

'--put this at the top of the form's module in the declarations section
Private intLogonAttempts as Integer


'-- code for Click event of cmdEnter
Public Sub cmdEnter_Click()
    Dim rst As DAO.Recordset
    Dim strSQL As String

    '-- test username given, exit sub if not
    If Nz(txtName) = "" Then
        MsgBox "You must enter a name",vbInformation, "No Name Entered"
        txtName.SetFocus
        Exit Sub
    End If

    '-- test password given, exit sub if not
    If Nz(txtPassword) = "" Then
        MsgBox "You must enter a password",vbInformation, "No Password Entered"
        txtName.SetFocus
        Exit Sub
    End If

    '-- create query to get record for specifed username
    strSQL = "SELECT [UserPassword],[AccessLevel] FROM [tblUsers] WHERE [UserName]='" & txtName & "';"

    '-- run query
    Set rst = CurrentDB.CreateQueryDef("",strSQL).OpenRecordset

    '-- test if record found for given username, if not; incorrect username given
    If Not rst.EOF Then
        '-- test if password matches password in users record
        If rst("UserPassword") = txtPassword Then
            '-- correct password! now determine accesslevel, and open correct form
            Select Case rst("AccessLevel")
                Case "User"
                    '-- open user form
                    DoCmd.OpenForm("frmUserMenu")
                Case "Manager"
                    '-- open manager form
                    DoCmd.OpenForm("frmManagerMenu")
            End Select    
        Else
            '-- code to handle incorrect password
            MsgBox "Password Incorrect. Please try again",vbExclamation,"Password Incorrect"
            txtPassword.SetFocus
        End If
    Else
        '-- code to handle invalid username
        MsgBox "Invalid UserName given. Please Try Again",vbExcalamation,"Invalid UserName"
        txtUserName.SetFocus
    End If

    '-- close up recordset object
    rst.Close
    Set rst = Nothing

    
    '-- and finally, increment the logon attempt counter, quit access after 3 attempts!
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then DoCmd.Quit acSaveNone
End Sub

__________________________________________

the original code seemed to make a few assumptions that were not specified in the code you posted. my code above is all (*hopefully*) self-explanitory... well i included some comments I also included the DoCmd.OpenForms(...) statment for opening forms for you

Hope that helps some.

axa

[This message has been edited by axa (edited 08-13-2001).]

 

[gyli84]

Thanks for all the help, your code worked beautifully! Eventually I will have to secure the database using access user level security and workgroups so in effect someone might have to log into my system twice. Some people have said that it might be possible to achieve a login system and get the program to direct the user to a form dependent on their user level security access level by using code to look at the currentuser(), load an appropriate form dependent upon whther the person logged in is a member of Admin or is just a User. Would it possible for it to also:

1) To be able to display the Name of the currentuser on a form by entering an expression in the control source or some other means (I have a "tblUsers" table whereby the UserID field is the same as the User Name for users logging into Access and the table also contains a "Name" field).

2) For a combo box on a certain form to only be enabled if the currentuser is a member of Admin.

Would you advise getting the user to log in twice or is what I want achievable using workgroups and another set of code?

In my tblUsers I have added a "Name" field with the name of the user. Is there a way of adding to the code you have given me in VB so that by using the user id of the person logging in I determine the users name and display it in a textbox on the form? Also I have realised that I would have to get the login form to popup at startup and users not to be able to close it (it would automatically close after login). Is this possible?

Yo've been a great help!

my email is gyli84@yahoo.com by the way

[This message has been edited by gyli84 (edited 08-14-2001).]

[This message has been edited by gyli84 (edited 08-14-2001).]

[This message has been edited by gyli84 (edited 08-15-2001).]

 

[merciless32]

Help!

Your code works just fine until I click the Login button. Then I get an "object is required" error in this portion:

'-- run query
Set rst = dbs.CreateQueryDef("", strSQL).OpenRecordset

What did I miss? Please Help. Here is the whole code.

----------------------------------------

Option Compare Database
Private intLogonAttempts As Integer

Private Sub Command10_Click()

Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

'-- User Name Required
If Nz(txtUser) = "" Then
MsgBox "You must enter a name", vbInformation, "No Name Entered"
txtUser.SetFocus
Exit Sub
End If

'-- Password Required
If Nz(txtPassword) = "" Then
MsgBox "You must enter a password", vbInformation, "No Password Entered"
txtUser.SetFocus
Exit Sub
End If


'-- User Record Query
strSQL = "SELECT [Password],FROM [tblPasswords] WHERE [User_Name]='" & txtUser & "';"

'-- run query
Set rst = dbs.CreateQueryDef("", strSQL).OpenRecordset

'-- Record Found
If Not rst.EOF Then

'-- Password Check
If rst("Password") = txtPassword Then

'Open The Student Form
DoCmd.OpenForm "Student Form"

Else

'-- Incorrect Password
MsgBox "Password Incorrect. Please try again", vbExclamation, "Password Incorrect"
txtPassword.SetFocus

End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If


'Close Login Form
DoCmd.Close acForm, "Login", acSaveNo

End If

End Sub

 

[merciless32]

Help!

Your code works just fine until I click the Login button. Then I get an "object is required" error in this portion:

'-- run query
Set rst = dbs.CreateQueryDef("", strSQL).OpenRecordset

What did I miss? Please Help. Here is the whole code.

----------------------------------------

Option Compare Database
Private intLogonAttempts As Integer

Private Sub Command10_Click()

Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

'-- User Name Required
If Nz(txtUser) = "" Then
MsgBox "You must enter a name", vbInformation, "No Name Entered"
txtUser.SetFocus
Exit Sub
End If

'-- Password Required
If Nz(txtPassword) = "" Then
MsgBox "You must enter a password", vbInformation, "No Password Entered"
txtUser.SetFocus
Exit Sub
End If


'-- User Record Query
strSQL = "SELECT [Password],FROM [tblPasswords] WHERE [User_Name]='" & txtUser & "';"

'-- run query
Set rst = dbs.CreateQueryDef("", strSQL).OpenRecordset

'-- Record Found
If Not rst.EOF Then

'-- Password Check
If rst("Password") = txtPassword Then

'Open The Student Form
DoCmd.OpenForm "Student Form"

Else

'-- Incorrect Password
MsgBox "Password Incorrect. Please try again", vbExclamation, "Password Incorrect"
txtPassword.SetFocus

End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If


'Close Login Form
DoCmd.Close acForm, "Login", acSaveNo

End If

End Sub

 

[Autoeng]

To display the current user on a form field use

=CurrentUser()

To use usergroups to allow forms / buttons, ect...

If InStr(UserGroups(), "Admins") <> 0 Then

For example to make a button usable only for the Admins usergroup

If InStr(UserGroups(), "Admins") > 0 Then
        Me.yourComboBox.Enabled = True    
Else
        Me.yourComboBox.Enabled = False
End If

Replace yourComboBox with the name of your combo box. Enabled could be replaced in both statements with Visible if you didn't want it to be seen if not available.

Autoeng