Log in form problems

[Tomino]

Hi,

I am working on a log in form for an Access 2003 db. Because the built-in jet database engine doesn't supply the ability to record every log attempt, failed attempt, validate passwords, user lock after X failed log attempts, ... I desided to program one of my own.

The code itselves works fine but I cannot write the value of intLogonAttempt to tbl_users, I would like to lock the user after 3 failed log attempts, and made a field called [Attempts] into tbl_users, which count every faild log attempts and a field called [lock_user] as boolean to activate the user lock.

I can count with intlogAttempts but I cannot write the value to the tbl to use to count every failed log on attempt, it always goes back to zero and even with a macro It doesn't work.

My goal is to,create a complete log on record where the db will write every log on (failed or succesfull) to a record, as well as every change of password, new users, deactivated users,....

Help is greatly appriciated.

thanks,
Tom

' Module   : mod_display_menu
' Function : display the switchboard after checking the access and password and password expiry date
 
Option Compare Database
Option Explicit
 
Sub display_menu()
On Error GoTo err_display_menu
 
 
 
'*************************************************************
'*          Check userId and access level                    *
'*************************************************************
Dim access_level As Integer
Dim finish_Date As Date
Dim port_syd As String
Dim valid_user As Integer
Dim check_user As Integer
Dim password_period As Date
Dim check_password As String
Dim strmsg As String
Dim bln_lock As Boolean
Static intLogonAttempts As Integer
 
valid_user = 2
 
 
 
 
' ***********************************************
' *             Validate user_id                *
' ***********************************************
check_user = DCount("[user_id]", "tbl_users", "user_id=forms!frm_main!user_id")
bln_lock = DLookup("[Control_lock]", "tbl_users", "user_id=forms!frm_main!user_id")
    If check_user = 1 And bln_lock = False Then
        valid_user = 2
    Else
        valid_user = 0
   End If
 
 
 
 
' ********************************************************************************
' *         Validate password                                                    *
' *         REMARK: Changed UCase scenario to allow upper and lower case symbols *
' ********************************************************************************
If valid_user = 2 Then
   check_password = DLookup("[passwords]", "tbl_users", "user_id=forms!frm_main!user_id")
   If check_password = Forms!frm_main!password Then
        valid_user = 2
   Else
        valid_user = 1
   End If
 
End If
 
 
 
' **********************************************
' *         Validate access_level              *
' **********************************************
If valid_user = 2 Then
    access_level = DLookup("[access_level]", "tbl_users", "user_id=forms!frm_main!user_id")
End If
 
Select Case valid_user
 
    Case 0, 1
            strmsg = " Access Denied" & _
                        vbCrLf & " Contact your Administrator if the problem persists.   "
            MsgBox strmsg, vbCritical, "INVALID USER ID or PASSWORD"
 
            intLogonAttempts = DLookup("[Attempts]", "tbl_users", "user_id=forms!frm_main!user_id")
 
            intLogonAttempts = intLogonAttempts + 1
 
 
            'write intlogonAttempt to [Attempts] in tbl_users
 
 
            If intLogonAttempts > 3 Then
                MsgBox "You are locked out this database.  Please contact your System Administrator.", vbCritical, "Restricted Access!"
                bln_lock = True
                'Application.Quit
            End If
 
 
           ' DoCmd.Quit
 
    Case 2
            Select Case access_level
               Case 1   ' Level1 menu; System Administrator
                        ' validate password expiry
                      password_period = DLookup("[password_date]", "tbl_users", "user_id = forms!frm_main!user_id")
                      If password_period < date - 30 Then
                            strmsg = " Your password has expired. You must change your password"
                            MsgBox strmsg, vbInformation, "Expired Password"
                            DoCmd.OpenForm "frm_change_password", acNormal
                        Else
                            DoCmd.OpenForm "switchboard"
                        End If
 
               Case 2   ' Level2 menu; super visor acount
                        ' validate password expiry
                      password_period = DLookup("[password_date]", "tbl_users", "user_id = forms!frm_main!user_id")
                      If password_period < date - 90 Then
                            strmsg = " Your password has expired. You must change your password"
                            MsgBox strmsg, vbInformation, "Expired Password"
                            DoCmd.OpenForm "frm_change_password", acNormal
                        Else
                            DoCmd.OpenForm "switchboard"
                        End If
 
               Case 3   ' Level3 menu; user acount
                        ' validate password expiry
                      password_period = DLookup("[password_date]", "tbl_users", "user_id = forms!frm_main!user_id")
                      If password_period < date - 90 Then
                            strmsg = " Your password has expired. You must change your password"
                            MsgBox strmsg, vbInformation, "Expired Password"
                            DoCmd.OpenForm "frm_change_password", acNormal
                        Else
                            DoCmd.OpenForm "switchboard"
                        End If
                Case Else
                        strmsg = " Access Denied" & _
                                    vbCrLf & " Contact your Administrator if the problem persists.   "
                        MsgBox strmsg, vbInformation, "INVALID USER ID or PASSWORD"
            End Select
 
End Select
 
exit_display_menu:
    Exit Sub
 
err_display_menu:
    MsgBox Err.decsription
    Resume exit_display_menu
 
End Sub
 
 
 
 
 
 
' ***********************************************************
'   Form: frm_main
'
'   Function :  login screen
'
'   Date last Modified : 26/02/2007
'   26/02/2007 - redesign for Sydney use. Sydney's menu now in a
'                      switchboard format
'
' ***********************************************************
 
Option Compare Database
Option Explicit
Private intLogonAttempts As Integer
 
' ******************************************************
' close form
' ******************************************************
Private Sub close_form_Click()
On Error GoTo Err_close_form_Click
 
    DoCmd.RunMacro "macro_exit"
 
Exit_close_form_Click:
    Exit Sub
 
Err_close_form_Click:
    MsgBox Err.Description
    Resume Exit_close_form_Click
End Sub
 
' ***********************************************************
'   User has entered user_id and Password
' ***********************************************************
Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
 
    ' Validate User ID and Password and then display menu items
      display_menu
 
Exit_cmdOK_Click:
    Exit Sub
 
Err_cmdOK_Click:
    MsgBox Err.Description
    Resume Exit_cmdOK_Click
End Sub
 
' ******************************************************
' maximize the screen
' ******************************************************
Private Sub Form_Load()
On Error GoTo Err_Form_Load
 
    DoCmd.Maximize
 
Exit_Form_Load:
    Exit Sub
 
Err_Form_Load:
    MsgBox Err.Description
    Resume Exit_Form_Load
 
End Sub
 
Private Sub Form_Open(Cancel As Integer)
 
'Reset Logon attempts on open form
intLogonAttempts = 0
 
End Sub
 
' ********************************************************************************
' when the user id gets the focus, the user id and password are reset to blank   *
' ********************************************************************************
 
Private Sub user_id_GotFocus()
On Error GoTo Err_user_id_GotFocus
 
    'remove current user and password
    Me!user_id = Null
    Me!password = Null
 
Exit_user_id_GotFocus:
    Exit Sub
 
Err_user_id_GotFocus:
    MsgBox Err.Description
    Resume Exit_user_id_GotFocus
End Sub

[NeoPa]

Please visit this thread to see why we would rather you didn't double-post (Please do Not Double Post).

Admin.

[NeoPa]

Tom, your question needs to be more specific.
Your code needs to be far more targetted.

You need to say what is not working and where in your code this problem occurs.

Whatever communication you have with anyone, don't assume they have read all of your code. It is quite unreasonable to expect anyone to go to that much truble (237 lines of code) for you for free.

I'll leave this with you for now.

[Tomino]

Your right, it's a bit tomuch code to read through ;-)

I look up a value in field [Attempts] out of table tbl_user with Dlookup and put it in var. intLogonAttempts


intLogonAttempts =

value of the looked up field [Attempts] need to be raised by 1:

DLookup("[Attempts]", "tbl_users", "user_id=forms!frm_main!user_id")
 
intLogonAttempts = intLogonAttempts + 1

After 3 failed logon attempts, the user needs to be locked:

If intLogonAttempts > 3 Then
                MsgBox "You are locked out this database.  Please contact your System Administrator.", vbCritical, "Restricted Access!"
                bln_lock = True

The problem is I can't write the new value of intlogAttempts (intlogAttempts + 1) to the existing field value.

I want to write intlogonAttempts + 1 to the field [Attempts] of user: [DLookup("[Attempts]", "tbl_users", "user_id=forms!frm_main!user_id")] so after 3 failed log in attempts, the user is locked out by changing value of field loclout of the user who tried to log in:

'write intlogonAttempt to [Attempts] in tbl_users
 
            If intLogonAttempts > 3 Then
                MsgBox "You are locked out this database.  Please contact your System Administrator.", vbCritical, "Restricted Access!"
                bln_lock = True
                'Application.Quit
            End If

Thank you very much for your help,

Regards,
Tom

[Tomino]

Hello,

I made a login form in Access using some code I found on the net. I would like the database to keep track of every failed log attempt per user and to lock the user after 3 failed log in attempts.
With recordsset I want to search in tbl_users how many failed attempts the user did to get in the database so I created in "tbl_users" a field called "Attempts" to keep track of every failed log in into the database.

here is the code:

' ' Module   : mod_display_menu
' Function : display the switchboard after checking the access and password and password expiry date
 
Option Compare Database
Option Explicit
Dim rsUsers As Object
Dim dbLimsFreeze As Object
Dim fldEnum As Object
Dim fldColumns As Object
 
 
Sub display_menu()
On Error GoTo err_display_menu
 
 
 
' *         Validate password                                                    *
 
 
If valid_user = 2 Then
   check_password = DLookup("[passwords]", "tbl_users", "user_id=forms!frm_main!user_id")
   If check_password = Forms!frm_main!password Then
        valid_user = 2
   Else
        valid_user = 1
   End If
 
End If
 
 
 
' **********************************************
' *         Validate access_level              *
' **********************************************
If valid_user = 2 Then
    access_level = DLookup("[access_level]", "tbl_users", "user_id=forms!frm_main!user_id")
End If
 
Select Case valid_user
 
    Case 0, 1
            strmsg = " Access Denied" & _
                        vbCrLf & " Contact your Administrator if the problem persists.   "
            MsgBox strmsg, vbCritical, "INVALID USER ID or PASSWORD"
 
            Set dbLimsFreeze = CurrentDb
            Set rsUsers = dbLimsFreeze.OpenRecordset("tbl_users")
            Set fldColumns = rsUsers.Fields
 
            ' Scan the records from beginning to each
            While Not rsUsers.EOF
            ' Check the current column
            For Each fldEnum In rsUsers.Fields
            ' If the column is named "Attempts"
            If fldEnum.Name = "Attempts" Then
            ' If the title of the current record is "user_id=forms!frm_main!user_id"
                If fldEnum.Value = "user_id=forms!frm_main!user_id" Then
            ' then change its value
                    rsUsers.Edit
                    rsUsers("Attempts").Value = +1
                    rsUsers.Update
                End If
            End If
        Next
            ' Move to the next record and continue the same approach
        rsUsers.MoveNext
    Wend
 
 
            If rsUsers("Attempts").Value > 100 Then
                MsgBox "You are locked out this database.  Please contact your System Administrator.", vbCritical, "Restricted Access!"
                bln_lock = True
                'Application.Quit
            End If

[NeoPa]

Tom, I don't know where you're going with the last post (#5). It seems little different from the first, except there are fewer lines. The question still implies it is the task of the expert to sort through all of your code, without any pointers from you, to determine your not very precisely specified problem.

Your earlier post (#4) is better, but still doesn't specify any line numbers of the code to indicate what you've tried, so we can see where you may be going wrong. It's very difficult to help someone if they don't explain their problem clearly.

As the question is so broad, all I can do is explain how you would update a value in a table if the form is not bound to that table.

That would be to create and execute some SQL of the form :

strSQL = "UPDATE [YourTable] " & _
         "SET [YourCount]=[YourCount]+1 " & _
         "WHERE [KeyField]='{Logon Name}'"

'{Logon Name}' is used here and is a string literal. You may well want to replace this with a reference to a form control (Me.UserName for instance). If so use instead :

strSQL = "UPDATE [YourTable] " & _
         "SET [YourCount]=[YourCount]+1 " & _
         "WHERE [KeyField]='" & Me.UserName & "'"

[NeoPa]

I left out the code for running the SQL. That would come after whichever of the other two bits of code you chose, and would be :

Call DoCmd.RunSQL(strSQL)