Show users loged in the database

[megatronixs]

Hi all,

I would like to create a form that will show the users connected to the database. It would be kind of a tracker who opened the database to work on some cases. The form could show all users from the datbase from top to bottom and to the right a green or red round dot that she/he is active opened the database or not active (closed the database or did not open it still)

I was thinking that it could be applied when the main form opens and that form captures the login from windows into the "tracker" form.
I guess it will only work when the "tracker" form is first open. If the "tracker" form is open later, how to capture those loged in earlier?

Any ideas or links where I could find more info?

Greetings.

 

[spikepl]

Don't forget googling - it is a goldmine of information!

The term to look for is access user roster

 

[burrina]

Try this link if you are a registered user.

http://www.utteraccess.com/forum/Who-s-Logged-Who-s-Con-t1897146.html

 

[megatronixs]

Hi Spkepl,

I did that, but no usefull info found. I tried the UserRoster, but that will not work out as the users are connected via a virtual desktop and it will show a computer name, but the number could be repeated a few times and it does not really shows me nothing this way.
I was hoping that when the user opens the main form, it will capture the user windows login and write this to a table and then show up in a form when the refresh button is pushed, and when the main form is closed, it will also capture the user windwos login.
So, any starting tips would be great

Greetings.

 

[megatronixs]

Hi burrina,

I will register later on from home and see if this will help out

big thanks for the link and I will post back with what I find.

Greetings.

 

[megatronixs]

Hi all,

Any one has an idea how to capture the open form and close form?
So there could be like "main form is opened by user Ptr-110"

Greetings.

 

[megatronixs]

Hi allm

Maybe I can ask it different:
I have a front end and a back end. every user has his copy of the front end. When they open the frm_Main, it should write into a table called tbl_UserFormOpen in the column "OpenForm" and when they close the form it should write to the table again but then in the column ClosedForm (or mabye just "True" or "False" and a separate form will show the user and next to it a rectangle with red or green background).

I'm sure the solution would be not that complicated.

Greetings.

 

[Gasman]

I did this in my last place of work, but without the bells and whistles that you want to show the status.

Mine had a hidden startup form that did the following.
Looked in the User table for the user's id. I was using the windows logon id.
If found, I updated a field to true. Field was called Logged.
On closing the database I retrieved the user record again and changed that field to false.

If the user id was not in the table then an error message was shown and the DB closed.

My team leader just viewed the User table in datasheet mode to see who had left themselves logged in, as despite asking them to log out each night, users were staying logged in, and preventing new changes to the DB being implemented.

This was all done in VBA.

 

[megatronixs]

Hi Gasman,

I try to capture this when some one opens the form and it will write to the table. but no clue how to do this :-(

I tried the below, but is missing an object:

If [tbl_UserFormOpen]![UserLogIn] = "PeterElk" Then
    Me.Box9.BackColor = RGB(0, 201, 87)
Else
    Me.Box9.BackColor = RGB(255, 0, 0)
End If

I try to run this from a form. So no clue where I go wrong.

Greetings.

 

[Gasman]

In the open event of your form you need somthing along these lines.

Note this was my code and you will need to change field names/table to match your system.

I happened to use Tempvars rather than global variable so I always had the userid and name to hand.

Private Sub Form_Open(Cancel As Integer)
'This form will control what form will be used as startup depending on the user name (FileID) in Windows.
' If an FL, they will get the control form, if PA, just the allocation form

Dim strOpenForm As String, strSQL As String, strUser As String, strRole As String, blnFlow_Leader As Boolean
Dim intRecordCount As Integer
Dim strReqPath As String

On Error GoTo Err_Handler

strReqPath = "C:\Program Files\PA Allocation"

'First make sure user is running their own accde file on their computer

If InStr(CurrentProject.Name, "accde") > 0 Then
    If CurrentProject.Path <> strReqPath Then
        MsgBox "Incorrect database opened, this will now close"
        Application.CloseCurrentDatabase
    End If
Else
    MsgBox "WARNING - This is not the accde file"
End If

'Using the TempVar collection for any global variables

TempVars("G_File_ID").Value = Environ("USERNAME")
TempVars("G_Full_Name").Value = ""


strUser = TempVars("G_File_ID").Value


strSQL = "SELECT User.Full_Name, User.Role,User.Flow_leader, User.Logged_In FROM User WHERE  (((User.File_ID)='" & strUser & "'));"

'MsgBox strSQL

Set rsUser = CurrentDb.OpenRecordset(strSQL)
' If no record found then EOF is true

If Not rsUser.EOF Then
    intRecordCount = rsUser.RecordCount
    TempVars("G_Full_Name") = rsUser.Fields("Full_Name").Value
    strRole = rsUser.Fields("Role").Value
    blnFlow_Leader = rsUser.Fields("Flow_Leader").Value
  Else
    MsgBox "Unable to locate User for User ID " & strUser
    Exit Sub
End If

' Now we set the logged in flag to identify anyone left logged in when tring to update/amend the back end.
With rsUser
    .Edit
    .Fields("Logged_in").Value = True
    .Update
End With


'Recordset and database now closed in Close event when database closes
' rsUser.Close
' Set rsUser = Nothing
' Set dbPA = Nothing

Then in the close event of the form

Private Sub Form_Close()
'    MsgBox rsUser.Fields("Full_Name")
' Now we clear the logged in flag to identify anyone left logged in when tring to update/amend the back end.

With rsUser
    .Edit
    .Fields("Logged_in").Value = False
    .Update
End With

rsUser.Close
Set rsUser = Nothing
Set dbPA = Nothing
Application.Quit acQuitSaveAll
End Sub

Start with something simple, then slowly increase the functionality.

For now I would say get the setting/clearing of whatever you are going to use to identify a logged in user working.

Hopefully my simple code will help you.

 

[megatronixs]

Hi Gasman,

I managed to implement more or less your above code. I used a query to write to the table users the "in" word when a user opens the database and the main form opens.
When they close the form, the unload action kicks in and updates the "in" to "Out".
So, when I open the table users, I see all who are in, or out.

Now comes the part of the form I want show, but an IF statment is not workin as it should (or maybe my limited knowledge is the problem). I have the below code, but does not work :-(
I use the table tbl_Users and the field Login and InOut. any clue what I do wrong?

Private Sub btn_Refresh_Monitor_Click()
If tbl_Users.Login = "PeterFla" And tbl_Users.[InOut] = "In" Then
Me.Box10.BackColor = RGB(0, 201, 87)
Else
Me.Box10.BackColor = RGB(255, 0, 0)
End If

End Sub

Any help to get this solved would be great

Greetings.

 

[Gasman]

I would be using the control fields of the form that hold Login and InOut data from the table?

So Me.Login and Me.InOut

 

[megatronixs]

Hi Gasman,
I'm using a label in a form to put the persons name and the box right to it that will change the color if in the table is "In" or "Out"

Greetings.

 

[megatronixs]

Just to be more specific, there will be a long list in the form that will show they are in our out.

Greetings.

 

[Gasman]

So why are we checking for a particular user?. Surely we just need to know whether a user in the record is In or out?

Regardless I would still be checking the form control bound to the field InOut.
You can have hidden fields if you do not wish to show the In or Out.

Hard to say without seeing the form and I am limited to Access 2003.

Due to 'the long list', I'd be inclined to have a dropdown for In and Out, and just look at one set at a time.

In my case I was only ever interested in those still logged in when workday was finished.

 

[megatronixs]

Hi Gasman,

At work we can't upload nothing to this site :-(
But I can discribe how it looks like.
There is the table users: tbl_Users where there are the "Login" field, the "Name" Field and the "InOut" field
Then I have a form that is called "frm_Activity_Monitor" where I have a label that I use to display the name of the user. Next to the name of the user there is a box with green or red color. When I click the refresh button, the vba should check if the login name is in the login column and if there is In or Out in the InOut column. If it is in, then the box becomes green.

Private Sub btn_Refresh_Monitor_Click()
If Me.Login = "PeterFla" And Me.InOut = "In" Then
    Me.Box10.BackColor = RGB(0, 201, 87)
Else
    Me.Box10.BackColor = RGB(255, 0, 0)
End If
End Sub

The above code works if the selected record is the one from PeterFla.


Greetings.

 

[gemma-the-husky]

 Private Sub btn_Refresh_Monitor_Click()

 If Me.Login = "PeterFla" And Me.InOut = "In" Then
    Me.Box10.BackColor = RGB(0, 201, 87)
Else
    Me.Box10.BackColor = RGB(255, 0, 0)
End If
End Sub

is this not working? if not, what goes wrong?
how do you call the function?

is the control set to transparent? the colour would be ignored in that case.

note that you can simply use (a limited range of) constants rather than RGB code
eg vbGreen and vbRed

 

[Gasman]

So the form only shows one record at a time.?

If so I would put your code in the Current event for the form, so as you move to each record the flag field (red/green) gets updated automatically.

However what happens when the user's name is FredBloggs?

That is why I am puzzled as to why you are testing for the user name.?

 

[megatronixs]

Hi all,

The activity form is just a blank form that will show all the names of the users in a label control. I have around 80 people using the database, and I would like to see who has it open. Maybe a subform inside of it jumps just to my mind.

Greetings.

 

[Gasman]

I would probably create a Tabular form with the wizard, then change the text field for Login to a label as you seem keen on labels, and have effectively a continuous form. No need for sub form.?

Regardless you will not be testing for a particular name in the code, else it will only ever work when that users record is processed.?