Record logout time on close

[PJFry]

I am developing a database and I would like to have the time that the user
exits recorded. I already have a login script that records their network
name and the time they logged in.

I started by putting an update SQL on the unload event of a hidden form that
is always open, but that didn't work.

Here is the code for recording the logout:

Private Sub Form_Unload(Cancel As Integer)
'Records the logout time.
Dim cID As Long

cID = DMax("ID", "tLog", "txtUserName='" & fOSUserName() & "'")

strSQL = "UPDATE tLOG SET dtmLogout = '" & Now() & "' WHERE ID = " & cID

DoCmd.RunSQL strSQL

End Sub

The code runs fine if I manually exit the form. But when the form is forced
closed because the database is closing, I don't have any luck. Where is the
proper place to code this so it will fire when the database shuts down?

Thanks!
PJ

 

[Graham Mandeno]

Hi PJ

I can't see why that doesn't work, but maybe you could try db.Execute
instead of DoCmd.RunSQL, as this will bypass Access and send the SQL command
directly to the Jet engine.

You might need to format the date and time because I'm not sure is Jet will
convert a string:

strSQL = "UPDATE tLOG SET dtmLogout = " _
& Format(Now, "\#yyyy-mm-dd hh:nn:ss\#" ) _
& " WHERE ID = " & cID

CurrentDb.Execute strSQL, dbFailOnError

After (if!?) you get it working, I would suggest removing the dbFailOnError
so you don't get difficult-to-handle errors as the database is closing.

 

[PJFry]

The only thing I can think of is that the form that this code runs on is
hidden. I ended up moving the code to the opening form and I disabled the
close button. It works fine there.

I did change my sql execution method to what you suggest. That does make
more sense.

Thanks for the help!
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.

Hi PJ

I can't see why that doesn't work, but maybe you could try db.Execute
instead of DoCmd.RunSQL, as this will bypass Access and send the SQL command
directly to the Jet engine.

You might need to format the date and time because I'm not sure is Jet will
convert a string:

strSQL = "UPDATE tLOG SET dtmLogout = " _
& Format(Now, "\#yyyy-mm-dd hh:nn:ss\#" ) _
& " WHERE ID = " & cID

CurrentDb.Execute strSQL, dbFailOnError

After (if!?) you get it working, I would suggest removing the dbFailOnError
so you don't get difficult-to-handle errors as the database is closing.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am developing a database and I would like to have the time that the user
exits recorded. I already have a login script that records their network
name and the time they logged in.

I started by putting an update SQL on the unload event of a hidden form
that
is always open, but that didn't work.

Here is the code for recording the logout:

Private Sub Form_Unload(Cancel As Integer)
'Records the logout time.
Dim cID As Long

cID = DMax("ID", "tLog", "txtUserName='" & fOSUserName() & "'")

strSQL = "UPDATE tLOG SET dtmLogout = '" & Now() & "' WHERE ID = " &
cID

DoCmd.RunSQL strSQL

End Sub

The code runs fine if I manually exit the form. But when the form is
forced
closed because the database is closing, I don't have any luck. Where is
the
proper place to code this so it will fire when the database shuts down?

Thanks!
PJ

 

[Stuart McCall]

The only thing I can think of is that the form that this code runs on is
hidden. I ended up moving the code to the opening form and I disabled the
close button. It works fine there.

I did change my sql execution method to what you suggest. That does make
more sense.

Thanks for the help!
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.

Hi PJ

I can't see why that doesn't work, but maybe you could try db.Execute
instead of DoCmd.RunSQL, as this will bypass Access and send the SQL
command
directly to the Jet engine.

You might need to format the date and time because I'm not sure is Jet
will
convert a string:

strSQL = "UPDATE tLOG SET dtmLogout = " _
& Format(Now, "\#yyyy-mm-dd hh:nn:ss\#" ) _
& " WHERE ID = " & cID

CurrentDb.Execute strSQL, dbFailOnError

After (if!?) you get it working, I would suggest removing the
dbFailOnError
so you don't get difficult-to-handle errors as the database is closing.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am developing a database and I would like to have the time that the
user
exits recorded. I already have a login script that records their
network
name and the time they logged in.

I started by putting an update SQL on the unload event of a hidden form
that
is always open, but that didn't work.

Here is the code for recording the logout:

Private Sub Form_Unload(Cancel As Integer)
'Records the logout time.
Dim cID As Long

cID = DMax("ID", "tLog", "txtUserName='" & fOSUserName() & "'")

strSQL = "UPDATE tLOG SET dtmLogout = '" & Now() & "' WHERE ID = " &
cID

DoCmd.RunSQL strSQL

End Sub

The code runs fine if I manually exit the form. But when the form is
forced
closed because the database is closing, I don't have any luck. Where
is
the
proper place to code this so it will fire when the database shuts down?

Thanks!
PJ

If the purpose of this hidden form is to preserve a database connection (a
lot of hidden startup forms are used for this), then presumably the
connection was terminated before the Unload event fired...