UtterAccess.com
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    
 
   
Savio
post Dec 27 2017, 10:15 PM
Post#1



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

savio
Go to the top of the page
 
ranman256
post Jan 2 2018, 10:35 AM
Post#2



Posts: 867
Joined: 25-April 14



You use a query for that too.

CODE
   '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
Post#3


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


welcome2UA.gif

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:

CODE
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
  Else
    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"
    Else
      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

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
http://www.accessmvp.com/DJSteele/AccessIndex.html
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
haresfur
post Jan 2 2018, 10:17 PM
Post#4



Posts: 277
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    17th July 2018 - 02:14 PM