Solved Log out when database closes using X

[donkey9972]

Hi,

So I have a question, I have a database that when a user logs in, it records their time, date, and status if they are logged in or not. Then I have a button for logging out. But as I am sure everyone knows people like to click the X. If they do this they database keeps their status as logged in until the use the log out button. Is there a way to update the status as logged out when someone decides to use the X instead of the button?

 

[NickCoe(UK)]

Hi,

So I have a question, I have a database that when a user logs in, it records their time, date, and status if they are logged in or not. Then I have a button for logging out. But as I am sure everyone knows people like to click the X. If they do this they database keeps their status as logged in until the use the log out button. Is there a way to update the status as logged out when someone decides to use the X instead of the button?

Which X ? The main Access window or one on a form? IIRC there is no trappable event when the Access window is closed. That X can be disabled forcing users to close via the form. But I've found some users then just use Task Manager to kill the Access task.

 

[cheekybuddha]

One way to do this is to hide your login form after successful login rather than close it.

Then, in its Form_Close() event, put code that will log the user out (if they are still logged in, and haven't already logged out using your logout button).

Then this code will fire when the db is closed and the hidden login form is closed automatically.

 

[donkey9972]

NickCoe I was referring to the main access window X. Cheekybuddha I will try this suggestion to see if it works and update either way.

 

[arnelgp]

agree with post #3.
have a a Hidden form open when the db starts (you'll need to open it using autoexec macro).
add code to it's Close event:

Private Sub Form_Close()

    ' check if the user has already logout
    If DCount("1", "LogData", "UserID = " & TempVars("UserID") & " And DateValue(LogInDateTime) = #" & Date & "# And IsDate(LogOutDateTime)") <> 0 Then
        ' already logout, so do nothing
    Else
        ' force to add logout date/time
        CurrentDb.Execute _
        "Update LogData Set LogOutDateTime = #" & Now() & "# " & _
        "Where UserID = " & TempVars("UserID").Value & " And DateValue(LogInDateTime) = #" & Date & "#"
        
    End If
    
End Sub

 

[donkey9972]

autoexec macro? I have never used that, never even heard of that before. I guess its name is self explanatory but how to use it, I am not sure, and where do I find it exactly? Guess I will need to do some googling on this part, but thank you arnelgp I will see what I can find out.

 

[donkey9972]

Well I have tried to use the code that you posted arnel and I just keep getting a syntax error:

Syntax error (missing operator) in query expression 'UserID = And DateValue(LogInDateTime) = #11/25/2024# And IsDate(LogOutDateTime)'.

I updated it to reflect my table field names but I still get this error.

"Syntax error (missing operator) in query expression 'LoginID = And DateValue(LoginEvent) = #11/25/2024# And IsDate(LogOutEvent)'."

I guess the search is still on.

 

[arnelgp]

if you analyze the error message, the LoginID is blank.

 

[donkey9972]

Ok I have figured out how to get it to log users out and input a time and date, but it is the wrong time and date. But I am having no luck with not having the LoginID being blank as you mentioned.

 

[The_Doc_Man]

To understand how & why this works, you must understand what is happening here. Clicking the Window [X] at upper right, you are terminating the (logged-in) session, which is comprised of one or more Windows tasks/processes. (You'll see either term, depending on who is writing the article. Most but not all modern references use "process".) A part of that session shut-down includes recursively closing every child process. And for child windows, if a form is open, it will go through the form's _Unload and _Close sequence. That would be two opportunities to do something. If one of those child windows is the "hidden but still open login form" then you can catch the event one of two ways.

1. Just log the event as a logout and be done with it.

2. In the _Unload event, CANCEL the event, which will disallow the form from closing, which will cascade to disallowing Access from closing.

I have used #2 successfully by intercepting that method of closure for ALL forms and disallowing any method of closure EXCEPT for the explicit [CLOSE] command button that set a flag saying "Close button was used". That meant that if you have multiple forms, you had to explicitly close each one. And instead of using a hidden login form, I had a "dispatcher" form that never closed. Child forms just always opened "on top". When the child forms were closed, you could click on the dispatcher's CLOSE button and all would calmly go away.

Note also that if someone abandons a session by just leaving Access running at the end of the day and there is an auto-shutdown policy in effect, Windows itself will click that button (internally) and start the sequence. BUT... you can tell Windows "NO" to a shutdown only once per process. After it politely asks you to shut down and you don't do so, out comes the sledgehammer that SHUTS you down anyway. If you want to understand the implications, look up "process rundown." Here's a discussion of a process rundown event, which is a Windows process event, not an Access application event.

In ETW, how to enable ProcessRundown events for Microsoft-Windows-Kernel-Process?

Provider's manifest indicates that it can send Microsoft-Windows-Kernel-Process::ProcessRundown::Info events, which I'd really like to have: they give a summary of processes that existed at the tim...

stackoverflow.com

 

[donkey9972]

Well I was able to get it to log the current time and date when it logs users out, but it changes the time and date for all users when they were logged out. I am at a loss now as to how to fix this.

Currently this is where I am at

    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE tblUsers SET LoggedIn=False"
    Me.Refresh

CurrentDb.Execute "Update tblLoginSessions Set LogOutEvent = #" & Now() & "# "

If anyone can help me tweak this so it does not change the logout times and dates of all the previous user logout times you would help me stop losing my hair, to late its already all gone. But I do need some help. I know arnel posted the code he did, but I am having no luck figuring it out.

 

[Minty]

Presumably you know the log in ID of whoever is logged in?

You need to use that as criteria for that update statement, otherwise it will simply update everyone (as you have discovered).
I suspect you also want to record that same ID in your login sessions table so you know who has logged in and out at a specific time?

 

[cheekybuddha]

@donkey9972 , remember we can not see your database.

We have no idea of the structure of your logins table.

We do not know what your login form looks like.

We do not know whether it is bound or unbound.

We can't see the code used to login.

We do not know how you store the currently logged in user once login has been completed.

Nor can we see the code attached your existing logout button.

Give us a clearer picture of what you have and we will be able to help you better.

 

[The_Doc_Man]

Well I was able to get it to log the current time and date when it logs users out, but it changes the time and date for all users when they were logged out. I am at a loss now as to how to fix this.

Currently this is where I am at

    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE tblUsers SET LoggedIn=False"
    Me.Refresh

CurrentDb.Execute "Update tblLoginSessions Set LogOutEvent = #" & Now() & "# "

If anyone can help me tweak this so it does not change the logout times and dates of all the previous user logout times you would help me stop losing my hair, to late its already all gone. But I do need some help. I know arnel posted the code he did, but I am having no luck figuring it out.

Your updates would work if you added a WHERE clause for each UPDATE query. One to update tblUsers ... WHERE USERID=... whatever the value of current user ID. The other to update tblLoginSessions ... WHERE SessionID = ... whatever the value of the current session ID. (You DO have sessionIDs, right?) If you don't have session IDs then you need ... WHERE SessionStart = ... whenever this session started AND userID = ... whoever started this session. (You DID keep those values around, didn't you? And you DO track them in the sessions table, right?)

 

[Mark_]

donkey9972, in addition to tracking when the user logs out, I'd suggest adding a timer event that updates a "Last alive" every 5 seconds (if you don't have a massive number of users). This helps with the other set of issues you'll have where they do something that would prevent your code from ever realizing they stopped using it (power off, loss of network connectivity, what have you).

Think through how many times a user doesn't or CAN'T log off due to reasons. "Last alive" helps with identifying this kind of issue.

 

[Pat Hartman]

@cheekybuddha gave you the correct solution. If you hide the login form when you log in rather than closing it, the login form will be the LAST form that closes when Access closes even if the user presses the "x" rather than one of your commands. PERIOD. So, you add code to the unload event of this form to update the data. OBVIOUSLY, you only update the record of the logged in user.