Help Recording LogOn/Off

[Dan2kx]

Function LogonLog(OnOff As String)
Dim rst As DAO.Recordset
If OnOff = "On" Then
    Set rst = CurrentDb.OpenRecordset("tblLogonLog", dbOpenDynaset, dbAppendOnly)
        rst.AddNew
          rst!StaffID = LogStaffID
          rst!Logon = Now()
        rst.Update
ElseIf OnOff = "Off" Then
    Set rst = CurrentDb.OpenRecordset("SELECT Max(tblLogonLog.LID) AS LID FROM tblLogonLog HAVING (((tblLogonLog.StaffID)=1) AND ((tblLogonLog.LogOff) Is Null));")
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("UPDATE tblLogonLog SET tblLogonLog.LogOff = Now() WHERE (((tblLogonLog.LID)=" & rst!LID & "));")
    DoCmd.SetWarnings True
End If
    rst.Close
End Function

Hello, i have some code that records logon/off time and date at logon/off and i think the log off is a bit sloppy, wondered if there was a better/more efficient way? of course i need to know the LineID (LID) to match on and off which is where my problem arises. one thing i wondered is can you read the value (autonumber) whilst appending by DAO (in the logon) so that i can store it as global variable?

thanks for any help

[ChipR]

You don't need to open a recordset for either of these things. For example, I keep the logon time in a global variable. Here's the code I've been using for a while.

Private Sub StartUp()
On Error GoTo ErrorHandler
    ...
    startTime = Now
    strSQL = "INSERT INTO UsageLog (StartTime, UserName, Workstation) VALUES (#" _
     & startTime & "#, """ & userName & """, """ & Workstation & """);"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL, 0
    DoCmd.SetWarnings True
    ...
    'Show Main Menu is in here somewhere
End Sub
 
'Form Unload for the Main Menu
Private Sub Form_Unload(Cancel As Integer)
On Error GoTo ErrorHandler
    Dim strSQL As String
    'record user log off
    strSQL = "UPDATE UsageLog SET EndTime = #" & Now & "# WHERE StartTime = #" & startTime & "#;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL, 0
    DoCmd.SetWarnings True
    ...
End Sub

[Dan2kx]

spose that would work, never thought about saving start time lol i was focused on the autonumber.

Thanks