Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2013
    Posts
    2

    Talking Updating a field based on another varibale using SQL

    I am currently writing some code for a 'Log In' form and I am now trying to add a few little features to make the form more secure/ user friendly. What I am struggling with now is adding a feature that will disable an account after 3 incorrect log in attempts. My code/ form uses DLookup to compare users usernames and passwords from a separate table 'Users' within this table I have a Yes/No field called 'Active'. I am trying to disable this record for the user attempting to login. My code is as follows:

    Option Compare Database

    Private LogonAttempts As Integer

    Private Sub Form_Load()

    LogonAttempts = 3 'Set Logon Attempts to 3 when database in openend

    End Sub

    Private Function Validation()

    'Check to see if data is entered into the Username text box

    If IsNull(Me.Username) Or Me.Username = "" Then
    MsgBox "Please enter a Username.", vbOKOnly, "Required Data"
    Me.Username.SetFocus
    Exit Function
    End If

    'Check to see if data is entered into the Password text box

    If IsNull(Me.Password) Or Me.Password = "" Then
    MsgBox "Please enter a password.", vbOKOnly, "Required Data"
    Me.Password.SetFocus
    Exit Function
    End If

    'Lookup Username in Login Table to confirm a valid Username (Case Sensitive)

    If StrComp([Username], DLookup("[Username]", "Users", "[Username] ='" & Me.Username & "'"), vbBinaryCompare) = 0 Then
    If DLookup("[Active]", "Users", "[Username] ='" & Me.Username & "'") = False Then
    MsgBox "Account Disable! Please Contact your System Administrator.", vbCritical, "Account Disable!"
    Exit Function
    End If
    Else
    MsgBox "Invalid Username. Please try again.", vbOKOnly, "Invalid Entry!"
    Me.Username.SetFocus
    Exit Function
    End If

    'Lookup Password for Username entered in Username (Case Sensitive)

    If StrComp([Password], DLookup("[Password]", "Users", "[Username] ='" & Me.Username & "'"), vbBinaryCompare) = 0 Then

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

    'Open Main Menu form
    DoCmd.OpenForm "Main Menu"

    Else
    'Verify the number of failed logon attempts
    If LogonAttempts = 0 Then
    MsgBox "Access Denied! Please Contact your System Administrator.", vbCritical, "Access Denied!"
    DoCmd.RunSQL "Update Users SET Active = False WHERE Username = " & Me!Username & ""
    'Application.Quit 'Quit Access if failed attempts reaches 3
    Else
    LogonAttempts = LogonAttempts - 1
    MsgBox "Invalid Password. Please try again. " & Chr(13) & " Remaining Logon Attempts = " & LogonAttempts & "", vbExclamation, "Invalid Entry!"
    Me.Password.SetFocus
    Exit Function
    End If
    End If

    End Function

    Private Sub Password_KeyDown(KeyCode As Integer, Shift As Integer)

    'Check to see if the return key was pressed and set Password value before Validation
    If KeyCode = 13 Then
    Me.Password.Value = Me.Password.Text
    Validation
    End If

    End Sub

    This section I am relying on change the necessary field is:

    DoCmd.RunSQL "Update Users SET Active = False WHERE Username = " & Me!Username & ""

    However this is currently presenting me with an additional pop-up box requesting the username to disable. I am trying to make this an automatic event?

    Any help would be much appreciated!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    try:-
    Code:
    DoCmd.RunSQL "Update Users SET Active = False WHERE Username = '" & Me!Username & "'"
    you need to delimit text values with the ' (or " characters
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2013
    Posts
    2

    Talking Thanks

    Thank you this worked a treat, knew it would be something simple.

    Much Appreciated!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •