Have you ever needed to work on an application and couldn't do so because someone had a lock on the database and was not at their desk? For me, the most frustrating situation is when I need to deploy a new version of a database on a client's server and I find that someone left the application open (locking the back-end) when they left their desk.
To overcome this, I developed this simple set of objects, one table (tbl_ForceLogoff) and two forms (frm_ForceLogoff, frm_ForceLogoffAdmin), which allow application users with admin permissions to force all users out of. It only takes a few steps to add this feature to any of your database applications.
1. Copy the table and two forms into your application
2. Add code to your startup form or macro to load frm_ForceLogoff as soon as your application loads
3. Add a method, a button on a form or an AutoKeys macro which will open frm_ForceLogoffAdmin. That's all there is to it.
tbl_ForceLogoff is a one record table and is the key to this entire process. It defines how frequently frm_ForceLogoff will check the value of the [ForceLogoff] field, determines how long the ForceLogoff form will be displayed, once the ForceLogoff value is checked, and displays the message entered by the administrator who set the ForceLogoff flag.
As the developer, you define the IntervalSeconds during application design. However, anyone with access to frm_ForceLogoffAdmin can also change this value at runtime), the other three values are entered using frm_ForceLogoffAdmin.
frm_ForceLogoff contains a timer event which allows it to check the state of the [ForceLogoff] field in tbl_ForceLogoff. This value is checked during the Form_Load event and on the interval set in the [IntervalSeconds] field of the table. If it [ForceLogoff] is set when the form loads, it will immediately open frm_ForceLogoff with a count-down of 30 seconds; this should be plenty of time for you to activate the AutoKeys macro, uncheck [ForceLogoff], and save the record. Although there is no code in the sample database to check this, I normally check to see whether frm_ForceLogoff is visible before opening any bound forms or creating a persistent connection to the back-end database. If it is visible, then I don't even bother moving off the Splash form or calling any additional code in my splash form. Since this form will generally be the first or second form opened and the last or second to last closed, you might also put your code which establishes a persistent connection to the back end in this form; I've done this in the sample database, even though there are no linked tables.
The TimerInterval of the form is set in the Form_Load event based on the value of the [IntervalSeconds] field in tbl_ForceLogoff (I usually set the timer interval at 5 minutes to give users sufficient time to finish working on whatever data entry form they are on at the time).
a. If that field value is false (0), the code just exits out of the timer event and waits for the next interval to occur.
b. if that field value is true (-1), the code changes the timer interval of the form to 1000 (1 second), makes the form visible and begins to count down for the number of minutes you define in the [ForceOffInMinutes] field of tbl_ForceLogoff.
Note: The hide button will hide the form for 30 seconds at a time.
c. When the count-down timer reaches zero, it calls a procedure that iterates through all open reports and forms (in reverse order of the sequence they were opened) and closes them. If a form is dirty, it clears (frm.Undo) all changes before closing it; this is necessary because most data entry forms have error checking in the Form_BeforeUpdate event which prevents users from saving the record if required fields are not filled in.
d. Once all of the open reports and forms have been closed, the code in frm_ForceLogoff performs a docmd. Quit to close the application.
The sample database loads frm_Splash and frm_ForceLogoff is loaded in the Form_Load event of frm_Splash.
In the sample, the splash form will stay open for the duration, because it contains the button for opening the ForceLogoffAdmin form, but normally this would be done with a button on an admin form or via AutoKeys macro (Ctrl-A in this case). The Orders form opens 10 seconds after the Splash form is opened, or when the user clicks the next button (>>).
Use this form to:
a. Change the TimerInterval (the number of seconds between each check by frm_ForceLogoff)
b. Change the status of Force logoff checkbox
c. Set the number of minutes for the count-down timer (can contain a decimal value)
d. Enter the message you want to be displayed to your users
Once you have checked the Force Logoff checkbox and clicked Save, frm_ForceLogoff will begin its countdown.
Disclaimer: In close to 10 years of using this technique, I cannot recall a single instance when this did not achieve my goal of booting all users out of an application. However, as several of my peers have pointed out, there are exceptions where this will not work.
Note: When you are done with your database maintenance, don't forget to reset the ForceLogoff flag, or you will get lots of calls from irate users!
Note: As a developer, I would modify the Form_Load code in frm_Splash to bypasses the loading of frm_ForceLogoff while I'm developing. Having that form open with the timer event running every so often will cause tons of problems as you are trying to type or edit code.
I hope you have enjoyed the article and can make use of the database.