Need Help with Tracking Users 3

[swaggel1]

I have searched the forums for the answer to my question, but I can't quite seem to find what I need.

I have created a secure database (using the Wizard) and the security features seem to be working fine. However, for tracking/usage monitoring purposes, I now need to track the activtiy of the database users. This tracking is very simple - I only need to record the user ID, date/time logging in, and date/time logging out.

Many of the threads I have found involve tracking changes to data. I don't need to know this level of detail, just user, and date/time logging in and out. I want to be able to store this information in a table for reference purposes.

I had hoped to set up something that would write to a "User" table when my (unbound) switchboard form was opened/closed. I tried to create a simple table, with a User field, login field, and logout field. However, at this point I'm confused about how to capture the user's ID and login/logout information. I tried to use the switchboard form's On Open and On Close events and use =CurrentUser and =Now() in some simple code, but I apparently didn't do this correctly.

Can anyone assist me? Thanks!

 

[PHV]

I tried to use the switchboard form's On Open and On Close events and use =CurrentUser and =Now() in some simple code, but I apparently didn't do this correctly.
Can you please post the code so we can see what is wrong with it ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[swaggel1]

This is the very simple code I tried to use on the "On Open" event:

Private Sub Form_Open(Cancel As Integer)
[Users]![User] = CurrentUser()
[Users]![Login] = Now()
End Sub

I get the following error message:
Run-time error 2465
Microsoft Access can't find the field "|" referred to in your expression.

I'm not sure what to do to correct this!

 

[PHV]

Try something like this:
[tt]Public myUser As String, myDate As Date
Private Sub Form_Open(Cancel As Integer)
myUser = CurrentUser()
myDate = Now()
DoCmd.RunSQL "INSERT INTO Users(User,Login) VALUES ('" _
& myUser & "',#" & myDate & "#);"
End Sub[/tt]
In the Close event procedure, add something like this:
[tt]DoCmd.RunSQL "UPDATE Users SET Logout=#" & Now() & "#" _
& " WHERE User='" & myUser & "' AND Login=#" & myDate & "#;"[/tt]

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[swaggel1]

Thank you so much! This is exactly what I needed, BUT.....
when I open and close the form, a dialog box appears asking if I want to append/update records. I don't want the users of the database to see this - I want it to occur invisibly for them. Is there any way to turn off this message and just have the Users table update automatically?

 

[idd]

swaggel1,

DoCmd.SetWarnings False

before the update occurs
and

DoCmd.SetWarnings true

at the end of the procedure or after the update occurs.

Hope that helps.

 

[swaggel1]

Thank you PHV and idd! A star for each of you! Between both of your posts, this is exactly what I needed and everything is working fine.

 

[bblekfeld]

Is there anyway to do the same thing but have it based on when the actual database is opened or closed instead of when the switchboard is closed?

Thanks

 

[idd]

bblekfeld,

I would create a form which is opened when the database is opened and that form remains hidden, i.e. the command used to open that form specifies that the form is hidden.

this can be done from

docmd.openform [form name]

one of the optional arguments which follows allows you to specify that the form remains hidden.

on that form have text boxes for user name, logon time & logoff time.

filter the the user name on form load to current user and set the logon time = now() then on the form unload event enter the code to add the logoff time, i.e. logoff = now()

note: I also make use of a field called active to indicate whether the user is currently logged on. Makes it easier to see the ticks in the boxes.

What happens is that the form is opened and the user's username is stored as well as the logon time when the database is opened. When the database is close by any means the hidden form will be made to close as well. This will trigger the unload event. In which it makes access record the logoff time in the logoff field.

the code I have recently used is given below


------------------------------------------------

Private Sub Form_Load()
Me.Filter = "username = fosusername()"
Me.FilterOn = True
Me.logon = Now()
Me.Active = True
Me.logoff = ""
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End Sub

--------------------------------------------------



Private Sub Form_Unload(Cancel As Integer)
Me.logoff.SetFocus
Me.logoff = Now()
Me.Active.SetFocus
Me.Active = False
End Sub


--------------------------------------------------

 

[DaleWatson123321]

idd,

A Star to you. Thanks for your example - it's much appreciated. ;-)

...Dale Watson dwatson@bsi.gov.mb.ca

 

[AJParkinson]

swaggel1, don't know if this might be overboard for what you want, but I've written a FAQ on something similar and could do with some feedback? If you've got time, please have a look. Its faq181-5070

Andrew