Sample - Use Hidden Form to Log Users

[janeyg]

Hi, I wanted to send a message to the person who created this sample database with a modification query but I don't have enough posts to do this, so my message would not send.

The below sample database called
Sample - Use Hidden Form to Log Users , http://www.access-programmers.co.uk/forums/showthread.php?t=203331

I think this a really great idea that I would like to add to my database. I am a novice when it comes to code and am trying to get my head around it. I wondered if there is a way to modify the form/code so that I can log more than one form? I have 5 forms that I would like to log user in and out of but I dont know how to adapt this to do that? Can anyone help?

thanks
janeyg

 

[GinaWhipp]

This sample is to Log when Users enter and exit the database, sounds like you want to use it for more than that. What exactly are you trying to Log?

 

[janeyg]

I wanted to log which users have opened and exited a form, I have 5 forms and wanted to log usage and users. Do you think this is possible?

 

[GinaWhipp]

Hmmm, never logged opening and closing of a form just field data changes... Thouhg you might be able to by putting something in On_Load of the form. Why the form and not the fieilds?

 

[boblarson]

To log someone opening a form and closing the form it would be fairly simple.

1, Create a table with these fields and datatypes:
Table Name: tblFormLog
LogID - Autonumber(PK)
User - Text
FormName - Text
LogOpenedForm - Date/Time - General
LogClosedForm - Date/Time - General

2. In a standard module put this code:

Function UserIn(strForm As String)
   Dim strSQL As String

   strSQL = "INSERT INTO tblFormLog ([User], [FormName], [LogOpenedForm]) " & _
               "VALUES(" & Chr(34) & VBA.Environ("UserName") & Chr(34) & _
               "," & Chr(34) & strForm & Chr(34) & ",#" & Now & "#)"

   CurrentDb.Execute strSQL, dbFailOnError
End Function

Function UserOut(strForm As String)
    Dim strSQL As String
    
    strSQL = "UPDATE tblFormLog SET tblFormLog.LogClosedForm = #" & Now() & "# " & _
             "WHERE tblFormLog.LogClosedForm Is Null AND " & _
             "tblFormLog.User=" & Chr(34) & VBA.Environ("UserName") & Chr(34) & " AND " & _
             "tblFormLog.FormName=" & Chr(34) & strForm & Chr(34)


   CurrentDb.Execute strSQL, dbFailOnError
End Function

Then in the ON OPEN and ON UNLOAD properties of each form put:

ON OPEN PROPERTY:
=UserIn([Form].Name)

ON UNLOAD PROPERTY:
=UserOut([Form].Name)

 

[GinaWhipp]

Thanks Bob!

 

[janeyg]

Thanks Bob, I will have a go at setting that up. Much appreciated!

 

[janeyg]

Hi Gina

I wanted to log the activity on forms as I want to reduce them but my boss feels they are needed so I wanted to monitor usage to see if this really is the case.
thanks for your help on this one.

 

[janeyg]

Thanks BOB, it works great. The only thing I had to modify was to remove the = from the form properties. For some reason it flagged those as an error but otherwise that has resolved my problem. Thanks so much for your help!
janeyg

 

[boblarson]

Thanks BOB, it works great. The only thing I had to modify was to remove the = from the form properties. For some reason it flagged those as an error

Probably because you put those in the event in the VBA Window:

and not in the actual property dialog.

 

[janeyg]

Oh, I see now. Thanks for your help on this one BOB.