Sign In/Sign Out Revised

[Guest]

I support a shared Access database and have the following code to record in
tbl_SignOn when somebody opens the program.

Function AddtoSignOnHistory()
On Error GoTo Err_AddtoSignonHistory
Dim strUser As String
Dim mydbase As Database
Dim rs As Recordset

Set mydbase = CurrentDb


Set rs = mydbase.OpenRecordset("tbl_SignOn")
strUser = Environ$("username")

rs.AddNew
rs![User] = strUser
rs![LogDate] = Date
rs![LogTime] = Time

rs.Update
rs.Close

Exit_AddtoSignonHistory:
Exit Function

Err_AddtoSignonHistory:
MsgBox Err.Description
Resume Exit_AddtoSignonHistory

End Function

The structure of the tbl_SignOn is:
ID Autonumber
UserName Text
Login Date Date/Time
Login Time Date/Time

I would like to add 2 fields - Logoff Date and Logoff Time so that I then
run a query showing who is currently signed on (LoginDate is not null and
LogOffDate is Null).

Question:
Is there an event which could be used which would run some code to record
the Log Off Date and Log Off time when somebody logs off (closes) the
database or do I have to rely on an Exit button off the Main Menu?

 

[Graham R Seach]

John,

No, there's no such event. The standard process for accomplishing this is to
have the AutoExec macro fire up a hidden form when the database starts up.
Code in the form's Open or Load event records the user (plus log-in time) in
a table. Code in the form's Unload or Close event records the log-off time
(because the form must close/unload before the database can shut down
(except in the case of catastrophic error).

To know which record should be updated when the database shuts down, you can
create a SessionID (which you store in the Registry or a global variable)
when you save the log-in details, and use the same SessionID to update the
table when the database shuts down.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Van T. Dinh]

If the Main Menu Form is always open until the user exits the database
application then you can use the Form_Unload Event of the Main Menu Form to
run the code.

The reason is that to close the database and exit from Access, the Form
needs to be closed first.

 

[Guest]

Create a form that opens when the user opens the database, and keep the form
hidden. In the Close event of this form, put the code to update your table.
The close event for the form will fire before the application closes, even if
you use the Close button on the application.

 

[Marshall Barton]

Just a couple of side comments in addition to Graham's
excellent response.

There is no reason to keep the date and time parts separate
fields. Put both the date and time in the same field:
rs!LogDateTime = Now

A Null log out date/time does not always mean someone is
still using the database. It could mean that Access was
terminated in a catastrophic way such as a power failure,
Cttrl+Alt+Delete or a crash. While this can muddy the
waters a little, it can also be useful in determining the
cause of a corruption, by identifying who crashed and
approximately when a crash occurred. This is also an almost
fool proof way to identify the idiot users that routinely
exit through Ctrl+Alt+Delete, never exit your application or
start multiple copies of it.

Another potential benefit of the Log table is that you can
use it to lock other users out of the database just by
opening an exclusive recordset to the table.

 

[David C. Holley]

You'll have to account for situations where an abnormal event occurred
such as the user turning off their PC or possibly if the user has to do
a CTRL-ALT-DEL. If either occurrs, or specifically if the hidden form
doesn't close as expected, and the user logs back on, you should see an
open-ended sign-on. (A record with a sign-on date/time, but no
sign-off.) To fix this, I would automatically run a query to update the
open-ended sign-ons that show them as signed-off. This will leave the
most recent sign-on as open. However, there may be situations where you
think a user is still logged in, but isn't. As in a PC locked up at
11:45am and the user went to lunch instead of logging back in. At
12:00pm you won't know if the person is in or not. To THAT end, I would
do something where the user's workphone, pager, cellphone whatever is
easily available along with they're physical location.

Create a form that opens when the user opens the database, and keep the form
hidden. In the Close event of this form, put the code to update your table.
The close event for the form will fire before the application closes, even if
you use the Close button on the application.

:

I support a shared Access database and have the following code to record in
tbl_SignOn when somebody opens the program.

Function AddtoSignOnHistory()
On Error GoTo Err_AddtoSignonHistory
Dim strUser As String
Dim mydbase As Database
Dim rs As Recordset

Set mydbase = CurrentDb


Set rs = mydbase.OpenRecordset("tbl_SignOn")
strUser = Environ$("username")

rs.AddNew
rs![User] = strUser
rs![LogDate] = Date
rs![LogTime] = Time

rs.Update
rs.Close

Exit_AddtoSignonHistory:
Exit Function

Err_AddtoSignonHistory:
MsgBox Err.Description
Resume Exit_AddtoSignonHistory

End Function

The structure of the tbl_SignOn is:
ID Autonumber
UserName Text
Login Date Date/Time
Login Time Date/Time

I would like to add 2 fields - Logoff Date and Logoff Time so that I then
run a query showing who is currently signed on (LoginDate is not null and
LogOffDate is Null).

Question:
Is there an event which could be used which would run some code to record
the Log Off Date and Log Off time when somebody logs off (closes) the
database or do I have to rely on an Exit button off the Main Menu?

 

[Guest]

Thanks, I will use the close form idea. That was very helpful.

John,

No, there's no such event. The standard process for accomplishing this is to
have the AutoExec macro fire up a hidden form when the database starts up.
Code in the form's Open or Load event records the user (plus log-in time) in
a table. Code in the form's Unload or Close event records the log-off time
(because the form must close/unload before the database can shut down
(except in the case of catastrophic error).

To know which record should be updated when the database shuts down, you can
create a SessionID (which you store in the Registry or a global variable)
when you save the log-in details, and use the same SessionID to update the
table when the database shuts down.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I support a shared Access database and have the following code to record in
tbl_SignOn when somebody opens the program.

Function AddtoSignOnHistory()
On Error GoTo Err_AddtoSignonHistory
Dim strUser As String
Dim mydbase As Database
Dim rs As Recordset

Set mydbase = CurrentDb


Set rs = mydbase.OpenRecordset("tbl_SignOn")
strUser = Environ$("username")

rs.AddNew
rs![User] = strUser
rs![LogDate] = Date
rs![LogTime] = Time

rs.Update
rs.Close

Exit_AddtoSignonHistory:
Exit Function

Err_AddtoSignonHistory:
MsgBox Err.Description
Resume Exit_AddtoSignonHistory

End Function

The structure of the tbl_SignOn is:
ID Autonumber
UserName Text
Login Date Date/Time
Login Time Date/Time

I would like to add 2 fields - Logoff Date and Logoff Time so that I then
run a query showing who is currently signed on (LoginDate is not null and
LogOffDate is Null).

Question:
Is there an event which could be used which would run some code to record
the Log Off Date and Log Off time when somebody logs off (closes) the
database or do I have to rely on an Exit button off the Main Menu?

 

[Guest]

THat's a great idea. Thanks

Just a couple of side comments in addition to Graham's
excellent response.

There is no reason to keep the date and time parts separate
fields. Put both the date and time in the same field:
rs!LogDateTime = Now

A Null log out date/time does not always mean someone is
still using the database. It could mean that Access was
terminated in a catastrophic way such as a power failure,
Cttrl+Alt+Delete or a crash. While this can muddy the
waters a little, it can also be useful in determining the
cause of a corruption, by identifying who crashed and
approximately when a crash occurred. This is also an almost
fool proof way to identify the idiot users that routinely
exit through Ctrl+Alt+Delete, never exit your application or
start multiple copies of it.

Another potential benefit of the Log table is that you can
use it to lock other users out of the database just by
opening an exclusive recordset to the table.
--
Marsh
MVP [MS Access]

I support a shared Access database and have the following code to record in
tbl_SignOn when somebody opens the program.

Function AddtoSignOnHistory()
On Error GoTo Err_AddtoSignonHistory
Dim strUser As String
Dim mydbase As Database
Dim rs As Recordset

Set mydbase = CurrentDb


Set rs = mydbase.OpenRecordset("tbl_SignOn")
strUser = Environ$("username")

rs.AddNew
rs![User] = strUser
rs![LogDate] = Date
rs![LogTime] = Time

rs.Update
rs.Close

Exit_AddtoSignonHistory:
Exit Function

Err_AddtoSignonHistory:
MsgBox Err.Description
Resume Exit_AddtoSignonHistory

End Function

The structure of the tbl_SignOn is:
ID Autonumber
UserName Text
Login Date Date/Time
Login Time Date/Time

I would like to add 2 fields - Logoff Date and Logoff Time so that I then
run a query showing who is currently signed on (LoginDate is not null and
LogOffDate is Null).

Question:
Is there an event which could be used which would run some code to record
the Log Off Date and Log Off time when somebody logs off (closes) the
database or do I have to rely on an Exit button off the Main Menu?