Creating a log in Access isn't 100% reliable, because if the application
crashes, or the computer loses power, or the application loses the network
connection to this table, then the appropriate entries aren't made in the
table. A client/server database is more reliable for logs.
If you still want to create the log in Access, then I would recommend making
a few changes to your table structure. First, create separate fields for the
UserName and ComputerName, change the Begin and End field names because these
are both Reserved words, and don't store a calculated value in the table
because it relies on values in other fields that, when changed, won't
automatically update this calculated field too. Someone will have to update
the calculated field manually, which is a maintenance nightmare. Intead of
storing the calculated value, calculate the value on the fly in a query or a
form.
I'd recommend that your table structure look like the following:
ID, AutoNumber, primary key
UserName, Text
ComputerName, Text
BeginTime, Date/Time
EndTime, Date/Time
To use this table as a log, create a new form and name it frmHidden. Use
this table (or better yet, a query based on this table) as its Record Source
Property, and add all of these fields as text box controls on the form. Open
the form in Design View and open the Properties dialog window. Select the
Event tab and scroll down until you see the Timer Interval Property. Set
this property to a low number like 10.
Copy the code on the following two Web pages into a new standard module:
http://www.mvps.org/access/api/api0008.htm
http://www.mvps.org/access/api/api0009.htm
Make sure that the API function declarations are moved to the Declarations
section of the module, not placed after any executable lines of code. For
example:
' * * * * Code Start * * * *
Option Compare Database
Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
' * * * * Code End * * * *
Copy the following code into your frmHidden form's code module:
' * * * * Code Start * * * *
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrHandler
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me!txtUserName.Value = fOSUserName()
Me!txtComputerName.Value = fOSMachineName()
Me!txtBeginTime.Value = Now()
RunCommand acCmdSaveRecord
Exit Sub
ErrHandler:
MsgBox "Error in Form_Open( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
Private Sub Form_Timer()
On Error GoTo ErrHandler
Me.Visible = False
Me.TimerInterval = 0
Exit Sub
ErrHandler:
MsgBox "Error in Form_Timer( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
Private Sub Form_Unload(Cancel As Integer)
On Error GoTo ErrHandler
Me!txtEndTime.Value = Now()
Exit Sub
ErrHandler:
If (Err.Number = 2448) Then
' Ignore, since the form is going into Design View.
Else
MsgBox "Error in Form_Unload( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If
Err.Clear
End Sub
' * * * * Code End * * * *
Replace the names of these text box controls with your own, then save and
compile the code.
Select the Tools -> Startup... menu to open the Startup dialog window.
Select the frmHidden form name in the Display Form/Page combo box. Select
the "OK" button to save the change. Save all of your work and close the
database application. Open the database application again and the frmHidden
form will open and record the log information, but after a very brief
"blink," it won't be visible to the user. When the database application
closes, this form will close, thereby saving the EndTime for this user's
session in the log table.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
