Login Tracker with VBA (1 Viewer)

wdhodges · Dec 3, 2014

So have created a database, and now I need a way to track who logs in. I have a tblLogInTracker with the following info on it.

LoginTrackerID
Username
Login Time
Login Date

When I press the 'Login' button on the login screen this is the code that I have:

Code:

Private Sub btnLogin_Click()
    Dim rs As Recordset
    
    Set rs = CurrentDb.OpenRecordset("tblUser", dbOpenSnapshot, dbReadOnly)
    
    rs.FindFirst "Username='" & Me.txtUsername & "'"
    
    If rs.NoMatch = True Then
        Me.lblWrongUser.Visible = True
        Me.txtUsername.SetFocus
        Exit Sub
    End If
    Me.lblWrongUser.Visible = False
    
    If rs!Password <> Nz(Me.txtPassword, "") Then
        Me.lblWrongPass.Visible = True
        Me.txtPassword.SetFocus
        Exit Sub
    End If
    Me.lblWrongPass.Visible = False
    
    If rs!AccessCode = 3 Then
       Dim prop As Property
       On Error GoTo SetProperty
       Set prop = CurrentDb.CreateProperty("AllowBypassKey", dbBoolean, False)
        
       CurrentDb.Properties.Append prop
       
SetProperty:
        If MsgBox("Would you like to turn on bypass key?", vbYesNo, "Allow Bypass") = vbYes Then
            CurrentDb.Properties("AllowBypassKey") = True
        Else
            CurrentDb.Properties("AllowBypassKey") = False
        End If

    End If
    
 ' The following code determines where the user will go based on their access code
   Select Case rs!AccessCode
    Case Is = 3
        MsgBox "You are entering Development Mode."
        DoCmd.OpenForm "frmDeveloperNavigation"
    Case Is = 2
        MsgBox "You are entering Admin Mode. If you have any problems please contact the development team at wdhodges@carilionclinic.org"
        DoCmd.OpenForm "frmAdminNavigation"
    Case Is = 1
        DoCmd.OpenForm "frmNavigation"
    Case Else
        MsgBox "You do not have access to view this database. If you would like access please email: wdhodges@carilionclinic.org"
   End Select
' End access user sorting

    DoCmd.Close acForm, Me.Name

How do I get this to track who logs in with the user name and place it with the time and date into the tblLogInTracker?

I'm new to VBA :/

CJ_London · Dec 3, 2014

Surprised you need to ask

Once the user is confirmed logged in you need to execute a append query - probably just after the 'Me.lblWrongUser.Visible = False' line

the code is

currentdb.execute "INSERT INTO tblUser (Username, [Login Date]) SET VALUES ('" & txtusername & "', " & Now() & ')'", dbfailonerror

note that you do not need a separate field for date and time, both values are stored in the same value which is a double type (i.e. has a decimal point), the date is to the left of the dp and the time to the right. time is expressed as fraction of 86400 (the number of seconds in a day)

wdhodges · Dec 3, 2014

CJ_London said:
Surprised you need to ask

Once the user is confirmed logged in you need to execute a append query - probably just after the 'Me.lblWrongUser.Visible = False' line

the code is

currentdb.execute "INSERT INTO tblUser (Username, [Login Date]) SET VALUES ('" & txtusername & "', " & Now() & ')'", dbfailonerror

note that you do not need a separate field for date and time, both values are stored in the same value which is a double type (i.e. has a decimal point), the date is to the left of the dp and the time to the right. time is expressed as fraction of 86400 (the number of seconds in a day)

I tried to add this code to it but it highlights my code red and doesn't seem to work.

:banghead:

CJ_London · Dec 3, 2014

suggest you check table and field name and file types
and if it highlighted red it would also have an error message

wdhodges · Dec 4, 2014

I've done a little research because I thought I was putting the code in wrong. It seems that the code is SQL and my database isn't SQL.. could this be the issue? I'll try it again and provide the error.

CJ_London · Dec 4, 2014

It seems that the code is SQL and my database isn't SQL.

Weird, how did you come to that conclusion?

wdhodges · Dec 4, 2014

When I kept getting the error I look up the

Code:

Insert Into

and found this: [https]msdn.microsoft.com/en-us/library/bb208861%28v=office.12%29.aspx

wdhodges · Dec 4, 2014

So I tried to enter the code again. This is what I got.

Run-time error '3134'
Syntax error in INSERT INTO statement.

Then I hit debug

And I have this

Code:

 CurrentDb.Execute "INSERT INTO tblLogInTracker (Username, (Login Date)) SET VALUES ('" & txtUsername & "', '" & Date & "'), dbfailonerror "

pbaldy · Dec 4, 2014

CJ_London said:
currentdb.execute "INSERT INTO tblUser (Username, [Login Date]) SET VALUES ('" & txtusername & "', " & Now() & ')'", dbfailonerror

Probably a typo, but the word "SET" can't be there. Also, if you concatenate Now() into the string, I suspect you need the # delimiter. Since Access will understand it, it can be in the literal text without, like

VALUES ('" & txtusername & "', Now())"

wdhodges · Dec 4, 2014

Wish I knew VBA better. :banghead: This is what I have now.

Code:

[COLOR="Red"]CurrentDb.Execute "INSERT INTO tblLogInTracker (Username, (Login Date)) VALUES ('" & txtUsername & "', '" & Now() & ')'", dbFailOnError[/COLOR]

CJ_London · Dec 4, 2014

PBaldy is right - my bad, must have been a late night

also noticed that

(Login Date)

should be [Login Date]

You've also added single quotes around now - recheck Paul's post

pbaldy · Dec 4, 2014

wdhodges said:
This is what I have now.

And?

Try

CurrentDb.Execute "INSERT INTO tblLogInTracker (Username, [Login Date]) VALUES ('" & txtUsername & "', Now()), dbFailOnError

wdhodges · Dec 4, 2014

CurrentDb.Execute "INSERT INTO tblLogInTracker (Username, [Login Date]) VALUES ('" & txtUsername & "', Now()), dbFailOnError

I got:
Run-time error '3137'
Missing semicolon (

at end of SQL statement.

pbaldy · Dec 4, 2014

Now I'm the one cranking out typos; forgot to terminate the string. Try

CurrentDb.Execute "INSERT INTO tblLogInTracker (Username, [Login Date]) VALUES ('" & txtUsername & "', Now())", dbFailOnError

wdhodges · Dec 4, 2014

It worked!!! Thank you guys so much!

pbaldy · Dec 4, 2014

We were happy to help!

wdhodges · Dec 4, 2014

How hard would it to be to do the same thing but when they log out?

pbaldy · Dec 6, 2014

Sorry, missed this. Did you sort it out? Wouldn't be hard, though you'd probably want another field to designate login vs logout. Or have both login and logout fields. When they logged out you'd update the record from their login to add the logout date/time.

CJ_London · Dec 6, 2014

How hard would it to be to do the same thing but when they log out?

The main issue you will have is if they don't logout - for example they simply close Access - so I would suggest you need to keep the login form open (tho' could be invisible) and do the logout update on the form close event

Login Tracker with VBA (1 Viewer)

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Registered User.

Wino Moderator

Registered User.

Super Moderator

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Super Moderator

Similar threads

Users who are viewing this thread