Better way of doing it?

[Acropolis]

Hi Guys,

I have been tinkering with Access for a few months now and have done a little with VBA but not massive amounts. Anyway I have made a system at work but it is largely based around forms that auto enter data into the tables open setting properties, saving and closing again on an open macro.

This I want to change, so I have started messing with VBA. I have re-written my login page, which comes up when you first open the DB.

It loads to frmMain, which has an onopen of If tempvar UserID is null then open frmLogin.

The code below is how I have re-written the code into VBA, it checks to make sure the user exists, if it doesnt it stops, then checks the password, if wrong set the failed log in attempt up by one, to a maximum of 3 then stops people logging in with that username, if password correct then resets the failedlogin to 0 if it is greater than 0.

It also creates an entry in the useractivitylog table, i went for seperate date and time as it is easier to combine them, than it is to seperate them I have found.

Anyway, it all works fine, I jsut wanted people opinion on it as to if there is a neater way of doing it than I have done so here?

The tables are stoed in a MySQL Server on the main office server and are linked tables via an ODBC link.

So your thoughts or opinions are appreciated.

Private Sub cmdLogin_Click()
UserID = DLookup("[ID]", "tblUsers", "[Username] ='" & Me.txtUsername & "'")
Password = DLookup("[Password]", "tblUsers", "[Username] ='" & Me.txtUsername & "'")
If IsNull(UserID) Then
    MsgBox ("User does not exist!")
    Me.txtPassword.Value = ""
    Me.txtUsername.Value = ""
    DoCmd.GoToControl "txtUsername"
Else
    If DLookup("[FailedLogInAttempt]", "tblUsers", "[Username] ='" & Me.txtUsername & "'") >= 3 Then
        MsgBox ("Account blocked, contact admin")
        DoCmd.Quit
    Else
        If Password = Me.txtPassword Then
            TempVars("tmpUserID").Value = UserID
            TempVars("tmpAccessLevel").Value = DLookup("[AccessLevelID]", "tblUsers", "[Username] ='" & Me.txtUsername & "'")
            If DLookup("[FailedLogInAttempt]", "tblUsers", "[Username] ='" & Me.txtUsername & "'") = 0 Then
 
            Else
                DoCmd.RunSQL "UPDATE tblUsers SET FailedLogInAttempt= 0 WHERE ID = " & UserID & ""
            End If
            DoCmd.RunSQL "INSERT INTO tblUserLog (UserID,UserActivityID,WhenDate,WhenTime) VALUES ('" & UserID & "',1,'" & Date & "','" & Time() & "')"
            DoCmd.Close acForm, "frmLogin", acSaveNo
            DoCmd.OpenForm "frmMain"
            MsgBox ("Log in successful")
        Else
            DoCmd.RunSQL "UPDATE tblUsers SET FailedLogInAttempt= FailedLogInAttempt + 1 WHERE ID = " & UserID & ""
            Remaining = 3 - DLookup("[FailedLogInAttempt]", "tblUsers", "[Username] ='" & Me.txtUsername & "'")
            If Remaining = 0 Then
                MsgBox ("Login attempts exceeded. Account Block, contact admin")
                DoCmd.RunSQL "INSERT INTO tblUserLog (UserID,UserActivityID,WhenDate,WhenTime) VALUES ('" & UserID & "',5,'" & Date & "','" & Time() & "')"
                DoCmd.Quit
            Else
                MsgBox ("Username or password incorrect. You have " & Remaining & " login attempts remaining")
                DoCmd.RunSQL "INSERT INTO tblUserLog (UserID,UserActivityID,WhenDate,WhenTime) VALUES ('" & UserID & "',4,'" & Date & "','" & Time() & "')"
                Me.txtPassword.Value = ""
                Me.txtUsername.Value = ""
                DoCmd.GoToControl "txtUsername"
            End If
        End If
    End If
End If
End Sub

 

[Pat Hartman]

It also creates an entry in the useractivitylog table, i went for seperate date and time as it is easier to combine them, than it is to seperate them I have found.

You just need to use the correct function. DateValue(YourDateTime) returns just the date. TimeValue(YourDateTime) returns just the time.

I prefer to use a bound form so I don't have to use all those DLookup() and update queries.

"Password" is a reserved word and shouldn't be used as a column name.

 

[Acropolis]

I tried the DateValue previously to pull the date on from a date&time (general date) field and in a wry, but had no look in seething any criteria against it at all.

 

[Cronk]

You have UserID and Date/time stored as strings?

viz
....VALUES ('" & UserID & "',4,'" & Date & "','" & Time() & "')..

 

[Acropolis]

I tried it without the ' first and it didnt work, works fine when I put them in.