X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
>  Login And Logout: How To Put Logout Time In The Correct Record?, Any Version    
post Dec 27 2017, 10:15 PM

Posts: 1
Joined: 27-December 17

Hi MAte,

I am trying to use the code in this discussion from 2014 in my database..

I have successful captured the login time as:

Private Sub Form_Load()

Dim StaffName As String
DoCmd.SetWarnings False
StaffName = Environ("StaffName")
strsql = "insert into tbl_LoginTime (StaffName,LoginTime)values('" & txtAllocatedtoCSA & "','" & Now() & " ')"
DoCmd.RunSQL strsql
End Sub

The Name of the user who opens up the form is captured from the "txtallocatedtoCSA" field. - this field displays the name of the user who opened the form.

now I need to UPDATE the SAME Table with the logout time for that user when he closes the form...

What VB do I use?

the logout table has fields as : LoginID , StaffName, LoginTime, LogoutTime

Go to the top of the page
post Jan 2 2018, 10:35 AM

Posts: 856
Joined: 25-April 14

You use a query for that too.

   'form level declaration
private mvStartTime

Private Sub Form_Load()
Dim StaffName As String

mvStartTime = Now()
DoCmd.SetWarnings False
StaffName = Environ("StaffName")  'THIS HAS NO USAGE HERE

'strsql = "insert into tbl_LoginTime (StaffName,LoginTime) values('" & txtAllocatedtoCSA & "','" & Now() & " ')"'
'DoCmd.RunSQL strsql
End Sub

private sub Form_Close()
dim vUserID

vUserID = Environ("Username")

strsql = "insert into tbl_LoginTime (LoginID , StaffName, LoginTime, LogoutTime) values('" & vUserID & "','" & txtAllocatedtoCSA & "',#" & mvStartTime & "#,#" & Now() & "#)"
DoCmd.RunSQL strsql
end sub

This post has been edited by ranman256: Jan 2 2018, 10:36 AM
Go to the top of the page
Doug Steele
post Jan 2 2018, 10:41 AM

UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


A very strong argument can be made for not trying to update the same record: to have separate records for log in and log out.

That being said, the following should accomplish what you're trying to do:

Private Sub Form_Unload(Cancel As Integer)

Dim dtmLastLogin As Date
Dim strStaffName As String
Dim strsql As String
Dim strWhere As String
  strStaffName = Me!txtallocatedtoCSA
  dtmLastLogin = Nz(DMax("LoginTime", "tbl_LoginTime", "StaffName = '" & strStaffName & "'"), 0)
  If dtmLastLogin = 0 Then
    MsgBox "Error: There is no corresponding Login record for " & strStaffName
    strWhere = "StaffName = '" & strStaffName & "' AND LoginTime = " & Format(dtmLastLogin, "\#yyyy\-mm\-dd hh\:nn\:ss\#")
    If IsNull(DLookup("LogoutTime", "tbl_LoginTime", strWhere) = False Then
      MsgBox "Error: " & strStaffName & " has already logged out"
      strsql = "UPDATE tbl_LoginTime " & _
        "SET LogoutTime = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#") & " " & _
        "WHERE " & strWhere
      CurrentDb.Execute strSQL, dbFailOnError
    End If
  End If
End Sub

BTW, read what Allen Browne has at Action queries: suppressing dialogs, while knowing results for why it's preferred not to use DoCmd.RunSQL

Go to the top of the page
post Jan 2 2018, 10:17 PM

Posts: 255
Joined: 4-April 12
From: Bendigo, Australia

Do you need to track logins which were not logged out gracefully?

If not, just store the login time in your code or form and insert both the login and logout when the form closes.

If you do need to keep track of logins when the user doesn't log out, after inserting the login record, I would store the "natural key" of StaffName and LoginTime to identify the record to update with the LogoutTime on exit.

This seems cleaner than searching for the maximum LoginTime and will guarantee that you are working with the record associated with the current session's login.

-- Evan
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 10:24 PM