You will need to log the time when the user started using the form, and the
time when they stopped.
1. Create a table with these fields:
- LogID AutoNumber primary key
- DocName Text name of the form
- OpenDateTime Date/Time when the form was opened.
- CloseDateTime Date/Time when the form was closed.
Save with the name "tblUsageLog".
2. Add a text box to the form, and give it these properties:
Name txtFormOpen
Format General Date
Visible No
On Load [Event Procedure]
3. Click the Build button (...) beside the On Click property.
Access opens the Code window.
Add this line to the event procedure:
Private Sub Form_Load()
Me.txtFormOpen = Now()
End Sub
4. Set the form's On Unload property to [Event Procedure], and add this code
to the event:
Private Sub Form_Unload(Cancel As Integer)
Dim strSql As String
Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#"
strSql = "INSERT INTO tblUsageLog (DocName, UserName, OpenDateTime,
CloseDateTime) SELECT """ & Me.Name & """ AS DocName, """ & CurrentUser() &
""" AS UserName, " & Format(Me.txtFormOpen, strcJetDateTime) & " AS
OpenDateTime, Now() AS CloseDateTime;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub
5. Use DateDiff() in a query to get the duration the form was opened.
Details in:
Calculating elapsed time
at:
http://members.iinet.net.au/~allenbrowne/casu-13.html
You may prefer to use the computer name or the Windows user name in place of
CurrentUser() above. If so, the code is in these links respectively:
http://www.mvps.org/access/api/api0009.htm
http://www.mvps.org/access/api/api0008.htm