How to create a Simple Login Tracking without a Login Form

TGoldenEye

How to create a Simple Login Tracking without a Login Form

In this How To, I will show how to create a simple easy login / logout tracking on MS Access without using a Login form. In this How To, we don’t have a login form to enter username or password to open MS Access file, but we can track the users who log in and log out by using the computer user login name. We can also track a host name or computer name at the same time.

In order to create the user login tracking, please follow the steps below.

Step 1 Create Log in/out Time table.

The picture below is the example of the table to save information of the user each time when logged in/ logged out. We need a UserLoginName field to save the user name. The Computername field is used to save the computer name that the user logged into.
What is Kamagra Oral Jelly100mg Sachets? Kamagra Oral Jelly is offered in semi fluid framed sachets which are effortlessly accessible on the web are frequently handled by little unregulated producers, regularly in improving nations that work with low overheads and work order generic cialis deeprootsmag.org costs and no administrative oversight. Generally these steps or methods involve daily work out plan, healthy diet for each and every day-time, adequate amount of sound and cozy sleep, pleased mental health and finally a great bonding with your female partner. levitra without prescription For instance, ED caused viagra canada overnight by stress and its production is blocked by the use of statin (cholesterol lowering) drugs. They can’t produce penetration properly due sildenafil overnight to their age.


Step 2 Create Login Procedure

Since we don’t use the Login Form in this example, we need to place the procedure on the form that is opened first and closed last such as Main form or Navigation form. In this example, I used the Navigation Form to capture the login information. My Navigation form will be opened until I close the program.

So I need to put the function/procedure under the On Load Event Procedure of this form to capture the information when user logged in.

Since I don’t have the Login Form, I will use an Environ$(“username”) system function to capture the username of user and will use an Environ$(“computername”) function to capture the computer name.

Example of Form On Load Event Procedure

Login Procedure:

 

Private Sub Form_Load()
Dim UserName, sHostName As String
Dim strInsert As String

UserName = Environ$("username") 'get user name
sHostName = Environ$("computername") ' Get Host Name / Get Computer Name
strInsert = "Insert Into tblLogTimes (userloginName,ComputerName,LoginTime)" & _
            "values ('" & UserName & "','" & sHostName & "','" & Now() & "');"
            CurrentDb.Execute strInsert, dbFailOnError
End Sub

 

Code Explanation:

We assigned the username to UserName variable and the host name or computer name to a sHostName variable. We use an Insert SQL command to add the login information to the tblLogTimes table.

SQL Insert function syntax =  Insert Into tablename (fieldname1, fieldnam2, fieldname3,…) Value (value1, value2, value3,…)

userLoginName            =  UserName or Environ$(“username”)

ComputerName           =  sHostName or Environ$(“computername”)

LoginTime                  =  Now()

All userLoginName and ComputerName fields are Text or String data type. So an apostrophe(‘) is needed in the Value of SQL like Values (‘ ” & UserName & ” ‘).

 

Open form

After the program and Navigation form is opened or loaded, the login procedure above is executed. The login information is updated to the userLoginName, ComputerName, and LoginTime fields on the tblLogTimes table as shown in the picture below. The LoginTime will be recorded as in the date and time format as using the Now() function. The LogOutTime field will be updated later when the Navigation form is closed.





Step 3 Create Logout Procedure

We need to place the procedure on the Navigation form under the On Close Event Procedure to capture the current time that the user closes program or closes this form.

Example of Form On Close Event Procedure

Logout Procedure:

 

Private Sub Form_Close()
Dim strSQL, UserName As String
Dim strUpdate As String
Dim rst As Recordset

UserName = Environ$("username") 'get user name
strSQL = "select * from tbllogtimes WHERE (UserLoginName = '" & UserName & "' and LogOutTime is null)"
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
    With rst
        .MoveFirst
        .Edit
        !LogOutTime = Now()
        .Update
    End With
Else
   'do nothing
End If
rst.Close
Set rst = Nothing
End Sub

 

Code Explanation:

In the Logout Procedure under the Form On Close event procedure, I used a Recordset method to update the Logout time instead of using the Update SQL command.

 

  • The strSQL = “select * from tbllogtimes WHERE (UserLoginName = ‘” & UserName & “‘ and LogOutTime is null)” is used to get a record (row) of the current user the logged in, but have not logged out yet. In this example, it will return a row of LogID 61.
  • Set rst = CurrentDb.OpenRecordset(strSQL) is opening a record of LogID 61 above.
  • If rst.RecordCount > 0 Then. Processing the next step if there is a record from the Recordset.
  • With rst …… End with – using the With …End With Statement in the Recordset to avoid writing the rst every time in front of the command like .MoveFirst instead of rst.MoveFirst.
  • Use .Edit and .Update –to update the value in the Recordset
  • !LogOutTime = Now() – updating a current date and time to the LogOutTime field

After the Navigation form is closed, the LogOutTime field is updated with the current date and time as shown below.

In this How To, you have learned how to use the SQL Insert command and the Update Recordset.



Related posts