Highlight currently logged users in access / Mark user as active at the moment

[Deleted member 147267]

Yes, Ihsang, long data type allows over 2 billion values above zero so not likely you will run out of values. Replication ID consumes a LOT of space, and the only reason for using it IMO is if you want to guarantee that a value will be unique. However, that uniqueness was intended to be across any computer using a db that might create an ID and that value needed to be integrated into the main data set. This field will make your db several times larger than if you used an autonumber, and the difference will be exponential.

When I worried about users logged in, my code added them when the logged in, and removed the record when they logged out. The max of possible users was 25, so the max records I could have had in the table at one time would be 25. I didn't care when anyone logged in/out, only if they were in. If that's all you need, then you don't need tens of thousands of login records. Whenever they did anything crucial, I stored the userID as part of the creation/edit record. In short, to me it didn't matter when anyone logged in/out, just mattered if they were in and what they did when they were.

 

[arnelgp]

added GUID (text) field to UsersActivity table.
added datasheet form UsersActivity with Timer event that requery the form.
this form has Conditional Format.
the form also is Order by TimeStamp Desc.
change the code of Public Sub Logging (plz see the code).
added Ubound (hidden) textbox to Login form, to save the GUID of the current Login user.
this textbox is set to null when he/she logouts.

open the UsersActivity datasheet form first, then the Logon form.

 

[theDBguy]

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

Hi. Thanks for posting a sample set of data. There's probably other and better ways of doing what you want done, but this is what I came up with.

Based on the data you provided, I had to create three queries: one for all logins, another one for all logoffs, and a union query to combine the two.

DBG_Logon

SELECT UsersActivity.UserIDD,
   UsersActivity.TimeStamp AS Logon,
   (select top 1 ua.timestamp from usersactivity ua where ua.activity="logoff" and ua.useridd=usersactivity.useridd and ua.timestamp<Nz((select top 1 timestamp from usersactivity ua where ua.useridd=usersactivity.[useridd] and activity="logon" and ua.timestamp>usersactivity.[timestamp] order by ua.timestamp),#12/31/9999#) and ua.timestamp>usersactivity.timestamp order by ua.timestamp desc) AS Logoff
FROM UsersActivity
WHERE (((UsersActivity.Activity)="logon"))
ORDER BY UsersActivity.UserIDD, UsersActivity.TimeStamp;

DBG_Logoff

SELECT UsersActivity.UserIDD,
   (select top 1 ua.timestamp from usersactivity ua where ua.activity="logon" and ua.useridd=usersactivity.useridd and ua.timestamp<Nz((select top 1 uab.timestamp from usersactivity uab where uab.useridd=usersactivity.[useridd] and uab.activity="logoff" and uab.timestamp>usersactivity.[timestamp] order by uab.timestamp),#12/31/9999#) and ua.timestamp<usersactivity.timestamp order by ua.timestamp desc) AS Logon,
   UsersActivity.TimeStamp AS Logoff
FROM UsersActivity
WHERE (((UsersActivity.Activity)="logoff"))
ORDER BY UsersActivity.UserIDD, UsersActivity.TimeStamp;

Union Query

select * from DBG_Logon
UNION select * from DBG_Logoff
ORDER BY useridd, logon, logoff

Hope that helps...

Edit: I had to adjust DBG_Logon a little bit to account for no logoffs. However, I think there's still something missing in the whole thing. I'll keep looking...

 

[Ihk]

Hi. Thanks for posting a sample set of data. There's probably other and better ways of doing what you want done, but this is what I came up with.

Based on the data you provided, I had to create three queries: one for all logins, another one for all logoffs, and a union query to combine the two.

DBG_Logon

SELECT UsersActivity.UserIDD,
   UsersActivity.TimeStamp AS Logon,
   (select top 1 ua.timestamp from usersactivity ua where ua.activity="logoff" and ua.useridd=usersactivity.useridd and ua.timestamp<Nz((select top 1 timestamp from usersactivity ua where ua.useridd=usersactivity.[useridd] and activity="logon" and ua.timestamp>usersactivity.[timestamp] order by ua.timestamp),#12/31/9999#) order by ua.timestamp desc) AS Logoff
FROM UsersActivity
WHERE (((UsersActivity.Activity)="logon"))
ORDER BY UsersActivity.UserIDD, UsersActivity.TimeStamp;

DBG_Logoff

SELECT UsersActivity.UserIDD,
   (select top 1 ua.timestamp from usersactivity ua where ua.activity="logon" and ua.useridd=usersactivity.useridd and ua.timestamp<Nz((select top 1 uab.timestamp from usersactivity uab where uab.useridd=usersactivity.[useridd] and uab.activity="logoff" and uab.timestamp>usersactivity.[timestamp] order by uab.timestamp),#12/31/9999#) and ua.timestamp<usersactivity.timestamp order by ua.timestamp desc) AS Logon,
   UsersActivity.TimeStamp AS Logoff
FROM UsersActivity
WHERE (((UsersActivity.Activity)="logoff"))
ORDER BY UsersActivity.UserIDD, UsersActivity.TimeStamp;

Union Query

select * from DBG_Logon
UNION select * from DBG_Logoff
ORDER BY useridd, logoff, logon;

Hope that helps...

I am very very thankful. Tomorrow I will apply all this procedure, I will update the status here.
Wish you good time. There are nice people in this world who help others. Thanks again.

 

[isladogs]

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

I will see what I can do.
However, as the code is fairly deeply embedded in my apps, it will take a while to separate out the relevant parts.
In the meantime. I suggest you do a forum or Google search for 'inactivity idle time' and 'kickout' or 'forced closedown'

 

[theDBguy]

Edit: I had to adjust DBG_Logon a little bit to account for no logoffs. However, I think there's still something missing in the whole thing. I'll keep looking...

Okay, I worked on it some more, and I think the attached is a better version than the previous one I posted. However, I still think it's too complicated, so there must be a better way than this. Also, I think the simplest design would be to have both in and out fields in the same record. Hope this helps though...

 

[theDBguy]

I would love to have these features.

Regarding those timeout features, have a look at this demo at UtterAccess.

UtterAccess.com

www.utteraccess.com