Are You There 3
By Richard Rost
2 days ago
Auto-Logout Users After Inactivity - Part 3 In this lesson, part three of the Are You There series, we will walk through enhancing the user inactivity detection system in Microsoft Access. We will discuss tracking which form and control the user is currently using by storing Screen.ActiveForm and Screen.ActiveControl information in TempVars, and show you how to reset the inactivity timer based on user movement between forms and controls. We will also mention possible issues when users remain in a single control, and briefly explain what additional improvements are covered in the extended cut for members. MembersIn the extended cut, we will make the system smarter by detecting if the user is typing or moving the mouse anywhere, even if they stay on the same form and field. I will show you how to add sound effects, speech alerts, and multiple warning stages before the logoff, creating a more robust and professional auto log off system. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesRecommended Courses
Keywords TechHelp Access, Are You There, auto log off, TempVars, Screen.ActiveForm, Screen.ActiveControl, timer event, status function, user activity detection, message box timer, form navigation tracking, control movement detection, multiple warning stages, sound effects, speech alert
Intro In this lesson, part three of the Are You There series, we will walk through enhancing the user inactivity detection system in Microsoft Access. We will discuss tracking which form and control the user is currently using by storing Screen.ActiveForm and Screen.ActiveControl information in TempVars, and show you how to reset the inactivity timer based on user movement between forms and controls. We will also mention possible issues when users remain in a single control, and briefly explain what additional improvements are covered in the extended cut for members.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. This is part three of my "Are You There?" series. We're logging off users if they walk away from the computer for more than an hour or whatever. If you haven't watched parts one and two yet, go watch those first and then come on back.
Alright, so we've got a pretty good system working. It's updating this guy's timer event and resetting it to an hour every time we have some kind of a status update. You can throw status updates all over your database, but your users might still find a spot where you didn't. They might be opening up, like, a customer form, just moving around between different things here. You can put it in the On Current event like I said before, but sometimes that's not always good enough.
So, what we can do is we can also check, track, and store where they are in the database, and what form they're on, and what control they're in. We can do that with Screen.ActiveForm and Screen.ActiveControl. We can save those in a couple of TempVars.
I know TempVars was a prerequisite for the message box timer video. If you haven't watched that yet, go watch my TempVars video.
Let's go to the status function where a lot of the work for this is being done. Here is status right here. Now, remember, I said we're going to add some stuff here. Whenever status is invoked, we're going to just keep track of where the user was at that moment. This is going to run often, so this will at least be one more check.
We're going to say TempVars. It will be "AreYouThereActiveForm" equals Screen.ActiveForm. You can't just say Screen.ActiveForm. Screen.ActiveForm is actually a form, and if you do just this, Access will yell at you. I don't know if it's a compile error or a runtime error. It'll be a runtime error. Trust me, I've run into this a million times because it'll say you can't assign an object to a TempVar. It only holds basic variable types like strings and stuff, so we have to send .Name. That's just something you have to do.
We'll do the same thing with the active control, so we know what form and control they're on. ActiveControl and ActiveControl.Name. That should be just one way to do it.
We're updating that TempVar every time the status is invoked.
Another thing we'll do is where we're actually checking that form timer - this guy: design view, in its timer event. Before we even get to this message box timer thing, we're going to check the active control and the active form. If those don't match what we saved in the TempVars, then we can say that we've also detected some user activity and we're going to reset the timer.
So, check active form and control up here. We probably should have put a note down here to pop up a message box with timer to ask if user is there. Before we even get to that, we're going to check to see if the form and control are different.
Now, these are set by the status function, which is the first thing running in the database. They shouldn't be null. If you want to add checks here to make sure they're not null, you can do that as well. I'm not going to do that because I know that status sets them already.
Basically, all we have to do here is kind of what we did below. We'll just say, if the active form is different or the active control is different, then status activity detected and the timer will reset. It'll just go back to another hour.
So, we can say if TempVars("AreYouThereActiveForm") is not the same as Screen.ActiveForm.Name or TempVars("AreYouThereActiveControl") is not the same as Screen.ActiveControl.Name (use a line continuation). We can copy this and just change it to control. Screen.ActiveControl is different, and this should be form. If the TempVars for active form is not the same as the active form's name or the TempVars for the active control is not the same as the control's name, then status user activity movement detected, timer reset, or whatever you want to put there, and then exit sub.
In other words, it'll never get down to this down here. You won't even see the popup as long as the system detects that they've moved, they've changed forms, or they've changed controls within the same form. In that case, we don't need to worry about it resetting.
Debug: Compile. Alright, we're good. Let's close this and reopen it. Save changes, yes.
Open it up. Now I'm going to open up the customer form over here. Moving from control to control normally wouldn't be enough to keep this guy alive because notice there's nothing happening in the status there, but I'm going to turn on that cheat for five seconds. Let's see what happens.
Cheat's on, five seconds. I'm not going to do anything and I should get my popup. User activity movement detected, probably because I changed to this control because I clicked the button. Let's see what happens now. Oh, it reset. We got to click it again.
Because remember, before I was up in the form and then I clicked the button. So the button click actually changed it there. Now it's running. Are you still there? Yes, I'm still here.
Now it went back to an hour. Here's what I'm going to do. I'm going to turn this on, but I'm just going to keep moving between these fields and it should detect that as movement.
Ready? Click, it's on. I'm just going to move between these different fields. I moved down to here. User activity movement detected and it reset to an hour again. So I'll do it again.
Actually, we need a better way to test this. I'm going to temporarily make the status box reset to five seconds as well. Don't forget to put this back though. We're going to pause this, we're going to have it so it's every five seconds regardless.
We'll do 5,000. All right, now I'm going to do that. It's going to reset at the five seconds. Let's test. Yeah, there. I'm still here.
I'm going to go over here now and move to a different field. Let's see if it detects the movement. All right, it detected. I'm going to move over here now. It detected it. I'm going to move up here. It still detects it.
If I stay here and don't move, that's it. Yeah, okay.
We're good. Let's turn that off now. Let's go back and put it back to an hour. I'm just going to do that and that. We'll do five seconds for testing, so you can have that in there as well.
All right, so now we've got two pretty good methods for detecting that the user is still in the database and still doing stuff. Is it perfect? No, but it's pretty good.
Those two things alone, between status and changing forms and controls, that's good enough.
Now, there could be another situation where the user is, like, let's say that this customer form has a really big note field on it, and they're using that as they're writing something long. They're writing an article. I have a database where I do a lot of writing in, like, captain's log. It's in an Access database. I do all my writing in that. I got my formatting and stuff, buttons and all that, and then when I'm done, I hit publish and it just sends it up to my website database.
But I could very easily be working in that single text box for maybe not an hour - it shouldn't take me an hour to write a captain's log - but I could see someone else, maybe a couple hours, are still working in that same field. They haven't hit save in a while.
We'll deal with that in the extended cut. In the extended cut for the members, we're going to make this system much smarter and a lot more polished. I'll show you how to detect if the user is typing or moving the mouse in any form, anywhere, even if they stay on the same form and in the same field the whole time. This is good for those big long notes fields, like you type a novel into.
We'll add some sound effects. I got some really cool Star Trek sound effects that I use, but I'll show you where you can find some sound effects online. We'll add some speech. I had the computer say, hey, you still there, dummy? No, we won't make it say dummy. We could make it say whatever you want.
We'll also build multiple warning stages, with like a red alert klaxon going off in the background. Give them 60 seconds that's calm and quiet, and then, hey, you're still there, and then have more seconds until it really goes bye-bye.
If you want a more robust and professional auto log off system, that's all covered in the extended cut. Silver members and up get access to all of my extended cut videos, all of them. There are lots of them. There are hundreds of them by now, thousands and tons of stuff to watch.
Gold members, as you've seen, can download the databases that I build in the TechHelp videos. All of my members get some free training and it's a big fun party on my website. So join and I'll tell you where the party is at.
But that is going to do it for your TechHelp video for today and for this series. Post a comment down below. Let me know if you're going to use this stuff, what you liked, what you didn't like, if you have any questions, or what you'd like to see in a future video.
That's going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the primary purpose of the "Are You There?" system described in this video?
A. To monitor user login credentials
B. To log off users who have been inactive for a specified time
C. To track all user database queries
D. To assign user permissions for forms
Q2. What are TempVars primarily used for in this technique?
A. To store Access form objects directly
B. To temporarily store the name of the active form and control
C. To permanently record all user actions
D. To reset user passwords
Q3. Why can't you directly store Screen.ActiveForm in a TempVar?
A. Because TempVars are inaccessible in Access VBA
B. Because it would cause a compile error immediately
C. Because TempVars only store basic variable types like strings, not object types
D. Because Screen.ActiveForm is not available in Access
Q4. How does the updated system detect user activity outside of explicit status updates?
A. By using embedded macros on every form
B. By checking if the active form or control has changed and comparing it to TempVars
C. By querying the system clock
D. By logging all network traffic
Q5. What happens when the system detects that the active form or control has changed?
A. The database locks all forms
B. The system logs the user out immediately
C. The inactivity timer is reset
D. A warning message is always shown
Q6. What scenario does the extended cut promise to address that the methods in this video do not?
A. Tracking which database report the user is viewing
B. Detecting if the user is typing or moving the mouse within a field for a long time
C. Encrypting all user input
D. Preventing users from changing form themes
Q7. What is a suggested benefit for Gold members mentioned in the video?
A. Free annual subscriptions to Access
B. Ability to download databases used in TechHelp videos
C. Extra support hours via phone
D. Early access to software updates
Q8. What potential issue is mentioned regarding long periods in a single field, such as a notes field?
A. The system might falsely log off active users
B. The field will auto-delete after an hour
C. The form will automatically close
D. The Access application will crash
Q9. According to the video, why were the status checks added to the On Current event and timer event?
A. To prevent users from editing forms
B. To help reliably detect user activity across various parts of the database
C. To force the users to log in frequently
D. To limit access to the database to one form at a time
Q10. Why is it important to compare the current active form and control to the saved TempVars values?
A. To know which report to run next
B. To determine if the user has changed form or control and thus is still active
C. To automatically create an audit log
D. To update field formatting dynamically
Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-B; 7-B; 8-A; 9-B; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone continues the "Are You There?" series, where I show you how to automatically log off users from your Access database if they are inactive for a certain period of time, such as an hour. This is part three of the series, so if you missed the first two parts, I recommend reviewing those before proceeding.
Up to now, we have a functional system that relies on updating a timer each time a status update occurs. You can include status updates at logical points throughout your database to catch user activity. However, there is always a risk that users will find situations where their actions are not being tracked, such as when they are simply moving around within a customer form without triggering any status updates. While you might consider using the On Current event to catch some of these actions, even that might leave gaps.
To enhance tracking, I suggest monitoring not only user actions but also their current position in the database, specifically which form and control they are using. This can be accomplished by using Screen.ActiveForm and Screen.ActiveControl. You can store the names of these objects in TempVars to keep a running record.
If you're unfamiliar with TempVars, I suggest watching my earlier video about them, as they're a prerequisite for some of the approaches I discuss here.
Next, let's examine the status function, which is where most of the monitoring is handled. Every time the status function is invoked, it records both the current active form and control by saving their names into TempVars. Remember, you have to store the .Name property for each, since TempVars only accept basic types like strings, not object references. Attempting to store the object itself results in a runtime error.
Now, with these TempVars being updated frequently, we can check for changes in user activity. In the timer event for your form, before displaying any message box asking if the user is still active, you want to compare the current active form and control with the values stored in TempVars. If there is a difference, you know the user has interacted with the database, so you reset the timer accordingly.
The logic is simple. Whenever the active form or control changes compared to what is stored, user activity has been detected. This triggers the activity status procedure and resets the timer, which essentially gives the user another hour before being prompted again. This approach means that as long as the user is moving between forms or controls, the inactivity timer keeps resetting, and the message box will never appear.
To confirm everything is working, test the setup by reducing the timer interval to something short, like five seconds. Move between various controls and forms to ensure the system detects the activity and resets the timer as expected. After confirming everything works, you can revert the timer back to your preferred interval, such as one hour.
After implementing these two checks, you will have a reliable method for detecting most typical user activity in your Access database. It's not flawless, but it significantly reduces the likelihood of an inactive user being missed.
However, there's one common situation where this system might still fall short. Suppose your form contains a large note field and the user spends significant time simply typing in that single field without moving to another control or form. This can happen in databases used for writing, for example, where users might stay in one text box for quite a while.
I address this scenario in the extended cut of the lesson exclusively for members. In the extended cut, I cover how to further enhance the tracking system to recognize keystrokes and mouse movements even if the user remains on the same control or form for extended periods. This is especially useful for forms with large free-form text fields. I also include instructions on incorporating sound effects and speech feedback, and how to build progressive warning stages - such as escalating alerts - to make the notification system more robust and user-friendly.
If you need a more professional auto logoff solution, along with extras like speech output or Star Trek-themed effects, all of that is available in the extended cut for Silver or Gold members. Gold members can also download the complete databases demonstrated in these TechHelp videos.
That wraps up today's TechHelp lesson and this series. Feel free to leave a comment with your thoughts, questions, or suggestions for future topics. I hope you found this helpful.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Tracking current form and control with Screen.ActiveForm and Screen.ActiveControl
Storing current form and control in TempVars
Updating TempVars on status function invocation
Comparing TempVars to active form and control for user activity detection
Resetting inactivity timer based on form or control movement
Implementing object-to-string conversion for TempVars
Testing timer reset by moving between form controls
Adjusting timer intervals for testing and production
Debugging and compiling changes to the timer logicArticle In Microsoft Access, you might need to automatically log off users after a period of inactivity. This can help maintain security and data integrity, especially in shared environments. One challenge is reliably detecting if the user is still actively working, not just sitting at a particular screen. To tackle this, I will walk you through a practical method that logs off users if they are inactive for more than an hour (or any duration you choose), even accounting for scenarios where they might move between forms or controls.
The core of this approach relies on two things: regularly updating a timer based on user activity, and detecting if the user moves to a different form or control within your Access application. The first part is handled by a status-update function triggered throughout your database (for example, after certain actions or navigation events), and the second can be monitored using the Screen.ActiveForm and Screen.ActiveControl properties, which tell you which form and control the user is currently interacting with.
To store which form and control the user last interacted with, you can use TempVars, a temporary variable storage in Access. You might already be familiar with TempVars from other applications such as creating timed message boxes. Here is how you can modify your status function so that, whenever it runs, it updates which form and control the user was last using.
The code looks like this:
TempVars!AreYouThereActiveForm = Screen.ActiveForm.Name
TempVars!AreYouThereActiveControl = Screen.ActiveControl.Name
It is important to use the .Name property because TempVars can only store simple data types like strings, not object references. Screen.ActiveForm and Screen.ActiveControl are objects, so attempting to store them directly in TempVars would result in an error. By storing only the .Name properties, you keep track of the user's position in a way that TempVars can handle.
Next, to check for user inactivity, you need to regularly check (typically in the Timer event of your main form) whether the user is still on the same form and control they were on last time the status was updated. If either of these changes, it counts as activity and you should reset your inactivity timer.
In your Timer event code for the primary form, you can add this logic:
If TempVars!AreYouThereActiveForm <> Screen.ActiveForm.Name Or _
TempVars!AreYouThereActiveControl <> Screen.ActiveControl.Name Then
' User activity detected: reset the timer
Call StatusFunction ' Whatever you call your timer-resetting code
Exit Sub
End If
By including this check, you ensure that as long as the user is moving between different forms or controls, the inactivity timer continues to reset and they will not be logged off. This method gives you a more robust way of detecting actual user activity than only relying on specific status updates tied to certain actions.
You should pepper your status function call throughout your application at logical points, such as in the OnCurrent event of your forms or after row updates, but this additional check using ActiveForm and ActiveControl makes your system more reliable. However, it is not absolutely perfect. For example, if a user spends an hour typing into a long notes field without moving between controls or saving, this approach might log them off. To avoid this, you would need even more advanced monitoring, like checking for keyboard input or mouse movement within a single control, but that involves more complex coding and is beyond the scope of this basic implementation.
To test your setup, you can reduce the timer interval to a short duration during development. This allows you to quickly confirm whether the timer resets when you move between fields or forms. For example, set your interval to five seconds, move between controls, and observe whether your inactivity message or form closes as expected.
Once you have confirmed everything works, return your timer interval to your desired inactive period, such as one hour.
To summarize, by tracking the active form and control using TempVars and checking for changes in the Timer event, you create a simple but effective system to detect user activity and log off inactive users in Microsoft Access. This approach covers most real-world usage scenarios and helps ensure that users are not accidentally logged off just for switching between parts of your application.
If you find you need even more robust solutions, such as detecting typing within a single control or implementing audible or spoken warnings, those would require further enhancements using VBA and possibly integration with external libraries or system API calls. But this method provides a solid foundation for most Access projects where session management is needed. |
