Highlight currently logged users in access / Mark user as active at the moment (1 Viewer)

[Ihk]

Hi,
I have multiple user login based access database. There is table with name""UsersActivity". I created a form based on this table.
I want to highlight users whose status is still logged in but not logged out yet from the the database.
There are three fields in form (taken from table), and a 4th unbound field for current user.
txtDateTime
txtUserID
txtUserActivity
txtCurrentuser (In this field I want to this user is active now).

Example table below. (Date and time is in Descending order- so latest up)

DateTime	User ID	Activity	Current User
	E	Logon
	A	Logoff
	C	Logon
	D	Logon
	B	Logoff

I want to mark as "Active" in Current user field. For Example in above table User "D" "C" and "E" are still active because they are still not logged off.
Any Idea please VBA, How can specify "txtuserID" with field of Activity which ID still equal to "logon" but not "logoff".
Thanks

 

[theDBguy]

Hi. Do you also store the date/time when the users log on and off? Just curious...

 

[Ihk]

Hi. Do you also store the date/time when the users log on and off? Just curious...

Yes, it is stored and table / form both are sorted as Descending order. (Latest time up in the table).
But there are some users who were logged in 30min (example) before but still they are not logged out. In between there are many other users were active/ inactive. So the logged in user will not always stay on top (especially who was logged in 30, 20 minutes before)

 

[isladogs]

Remove the CurrentUser field from your table.
Instead, do this in a query and use that for your form. Suggest you use a boolean (yes/no) field for this purpose based on logon/logoff times.
If logon time exists but there is no subsequent logoff time, the user is active so CurrentUser=True
When a logoff time is saved, that becomes false.

You can find something similar in my Password Login example app.

 

[Micron]

IMO this is often problematic unless you prevent users from closing the db abnormally, which you really cannot. When that happens you end up with users logged in who aren't. If you want login/out records that's one thing. If you want just current users that's another. BTW the post kinda reads like the db is not split, and if that's true I think it's the main reason why this is being asked.

 

[Ihk]

IMO this is often problematic unless you prevent users from closing the db abnormally, which you really cannot. When that happens you end up with users logged in who aren't. If you want login/out records that's one thing. If you want just current users that's another. BTW the post kinda reads like the db is not split, and if that's true I think it's the main reason why this is being asked.

Yes you are right.
But in my case my question is different and my db does not close abnormally. I have users logoff login more than 1000 records.
in my case
1) db is splitted -- every user has their own front end
2) They properly either logoff or exit application.
So their activity is recorded.

Now the question is in current running state, When I have to work on db, I want to approach user but before that - I want to see who is logged in (not yet logged off), So I can approach (for example).

 

[Ihk]

Remove the CurrentUser field from your table.
Instead, do this in a query and use that for your form. Suggest you use a boolean (yes/no) field for this purpose based on logon/logoff times.
If logon time exists but there is no subsequent logoff time, the user is active so CurrentUser=True
When a logoff time is saved, that becomes false.

You can find something similar in my Password Login example app.

Thank you, It is really a nice idea.
But I think this idea is only applicable when TimeForLoggoff is in different field of Table and TimeForLoggin is in different field of Table.
but in my case my table has only single field "DateTime".
In total my activity table has just three field other than ID.

In this case how yes or no is possible? if bring this to query.

 

[arnelgp]

which code are you using? is it the one that get "roster" from the be using connection.openschema?
or another.

 

[isladogs]

Thank you, It is really a nice idea.
But I think this idea is only applicable when TimeForLoggoff is in different field of Table and TimeForLoggin is in different field of Table.
but in my case my table has only single field "DateTime".
In total my activity table has just three field other than ID.
View attachment 83173
In this case how yes or no is possible? if bring this to query.

In that case, make use of the Activity field.
If the last record is for a Logon event, the user is active, if a Logoff event, they are not.
You can use a query to check the activity value for each ID corresponding to the latest (max) date time Vale.

BTW I agree with the earlier comments about users closing the app abnormally e.g. By switching off their computer or due to network interruptions. Having used a similar system to record logon/logoff times for over 15 years, I can assure you that such events will happen and it is advisable to allow for them

 

[onur_can]

You can check your users' login and logout with two sub-procedures. You can then see which users log in and out with the help of a query. You can know that they are active by listing the users who are logged in and not logged out. I give the sub procedure codes below. You can place the LogInUser subroutine in your application when the form opens first, and the LogOutUser subroutine in the event when your main form is removed. The rest is up to you.

Public Sub LogInUser()
' User Input
Dim strSQL As String
Dim UserNameW As String
UserNameW = Environ("username")
strSQL = " INSERT INTO tbl_logged " _
        & "(logusername,event,utime) VALUES " _
        & "('" & UserNameW & "', 'Input', Now());"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub

Public Sub LogOutUser()
' User OutPut
Dim strSQL As String
Dim UserNameW As String
UserNameW = Environ("username")
strSQL = " INSERT INTO tbl_logged " _
        & "(logusername,event,utime) VALUES " _
        & "('" & UserNameW & "', 'Output', Now());"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub

 

[Micron]

Your table doesn't readily support your requirements because the activity field is of no real value. To make this easier, they should be separate fields, not records. If you want to know if they're in or out then a query that returns users with nulls in the logoff field will tell you that. If all you ever want to know is if they're in or out, a simple checkbox field would suffice, but it will tell you nothing else. Isladogs elaborated on what I meant so naturally I agree, and sooner or later it will happen. Might take years, but it will if your db lives long enough.

There are lots of forum posts about forcing users out - you might want to look at them. If you implement a method, keep in mind that simply dropping someone's connection can cause data corruption.

Are you using replicationID type as your ID values? That data type eats up a lot of space and is kind of pointless for just record ID values. If in fact the db is using replication, you will not be able to upgrade Access beyond (I think) 2010. If you are using replication and you upgrade, you will not be able to open this db without an older version.

 

[theDBguy]

Thank you, It is really a nice idea.
But I think this idea is only applicable when TimeForLoggoff is in different field of Table and TimeForLoggin is in different field of Table.
but in my case my table has only single field "DateTime".
In total my activity table has just three field other than ID.
View attachment 83173
In this case how yes or no is possible? if bring this to query.

Hi. Can you post a demo version of your db with test data, so we can show you how to create the query? Thanks.

 

[Ihk]

Your table doesn't readily support your requirements because the activity field is of no real value. To make this easier, they should be separate fields, not records. If you want to know if they're in or out then a query that returns users with nulls in the logoff field will tell you that. If all you ever want to know is if they're in or out, a simple checkbox field would suffice, but it will tell you nothing else. Isladogs elaborated on what I meant so naturally I agree, and sooner or later it will happen. Might take years, but it will if your db lives long enough.

There are lots of forum posts about forcing users out - you might want to look at them. If you implement a method, keep in mind that simply dropping someone's connection can cause data corruption.

Are you using replicationID type as your ID values? That data type eats up a lot of space and is kind of pointless for just record ID values. If in fact the db is using replication, you will not be able to upgrade Access beyond (I think) 2010. If you are using replication and you upgrade, you will not be able to open this db without an older version.

I am really very thankful for nice and detailed instructions for my understanding. I am actually beginner in this field. Yes I 100% agree with you and @isladogs , if login session is interrupted because of any reason (eg computer shutdown, electricity break etc), system will definity show that user is logged in all the times...This is also very nice idea, when @isladogs said, it clicked to mind, that logoff and login should be be different fields of table, then query will determine null value.
But this to keep the record of this login logoff activity, is not that important for my institution at this point, no body is even concerned about this. But on other hand, user activity like edition / record entry is stamped by time and user ID. That is the most important for us.

Purpose of this in/out record is just like that, but there is no reason to drop someone's connection in between. But in future may I can create a message / notice system to users- That "Please logout, we want to work etc". At the moment the only thing is sometimes, I have to make modifications and there are sometimes users, who keep themselves logged in the whole day (though Idle), then I wanted to know who and who is. Then I can personally speak to those Please logoff, I have to work little bit. In that case db will be free to work with.

Replication ID as ID values: Yes I am using this, only to my little knowledge, I assume that it will work as infinite number rather normal ID values (may end up after certain time). Because just now if I count number of rows , in login activity table it is 5 times more than other main record tables in Just around 45 days. If this is the case, this table may cause overloading on db, if you suggest also there is possibility, later I can delete record of login activity older than 40 days. This complete system is built in 2019 version.
As far as user work is concerned, my complete system is just text based data entry, nothing else (in back end database).
What do you suggest, I would love to have knowledge. Thank you.

 

[Ihk]

which code are you using? is it the one that get "roster" from the be using connection.openschema?
or another.

No, I am using module for loginActity by Tempvars for user ID, which is then connected to login page. Then the record is saved in my table. But now from table record I want to know, who is not yet logged off as above table picture.

 

[isladogs]

The best way of managing users who stay logged in all day & night is to add an inactivity idle time feature.
If the user has been inactive for a specified time of your choice e.g. 20 minutes, a countdown procedure starts after which the user is automatically logged out unless they respond during that time.
I also have a kickout procedure which allows the program admin to close the application to all users when essential maintenance is required
When the kickout feature is activated, active users also have a specified time e.g. 5 minutes, to save their work before the app closes.

Using both features means the program admin can be certain nobody is still logged in

I would also advise against replication ID. I would be incredulous if using Long integer for ID values was insufficient for any application.

Long Integer — For integers that range from -2,147,483,648 to +2,147,483,647.

My apps have 200+ users for each client institution who login repeatedly every day for short or longer periods.
After 15+ years, none of my clients have hit that limit and none ever delete login activity

 

[theDBguy]

But in future may I can create a message / notice system to users- That "Please logout, we want to work etc". At the moment the only thing is sometimes, I have to make modifications and there are sometimes users, who keep themselves logged in the whole day (though Idle), then I wanted to know who and who is.

Hi. There are available sample dbs for that. By the way, did you see my last post? Can you provide us with a sample set of data to play with?

 

[Ihk]

Hi. There are available sample dbs for that. By the way, did you see my last post? Can you provide us with a sample set of data to play with?

Thank you, Yes I will upload it in 15minutes

 

[Ihk]

The best way of managing users who stay logged in all day & night is to add an inactivity idle time feature.
If the user has been inactive for a specified time of your choice e.g. 20 minutes, a countdown procedure starts after which the user is automatically logged out unless they respond during that time.
I also have a kickout procedure which allows the program admin to close the application to all users when essential maintenance is required
When the kickout feature is activated, active users also have a specified time e.g. 5 minutes, to save their work before the app closes.

Using both features means the program admin can be certain nobody is still logged in

I would also advise against replication ID. I would be incredulous if using Long integer for ID values was insufficient for any application.

My apps have 200+ users for each client institution who login repeatedly every day for short or longer periods.
After 15+ years, none of my clients have hit that limit and none ever delete login activity

1st of all I am very thankful. Because this knowledge and experience sharing is very helpful for me.
If you have sample db for these two procedures (1) inactivity time count down - logout (2) Notice to logout in 5min etc for maintenance.
that will be helpful. if not , or may be code to share. Then I will change the table fields to normal long Integer ID values, because it is just beginning, better to change now. Thank you.

 

[Ihk]

Thank you, Yes I will upload it in 15minutes

I have attached demo database.
Please Press shift key while opening.
USERS
A
B
C
Password of each is 1 (one as digit)
Thank you very much

 

[Ihk]

1st of all I am very thankful. Because this knowledge and experience sharing is very helpful for me.
If you have sample db for these two procedures (1) inactivity time count down - logout (2) Notice to logout in 5min etc for maintenance.
that will be helpful. if not , or may be code to share. Then I will change the table fields to normal long Integer ID values, because it is just beginning, better to change now. Thank you.

I would love to have these features.
I have attached sample DB in above ------