6.1 Save with Each Record the Name of the Last
Person Who Edited It and the Date and Time
6.1.1 Problem
Your application is used in a multiuser environment with
users regularly adding and editing records. Access keeps track of when an object
was created and last modified. However, it does not track this information at
the record level. With each record, you want to log who created the record, who
last edited the record, and the date and time associated with each of these
actions. Is this possible?
6.1.2 Solution
Access has no built-in feature that records who edited a
record and when the edit was made, but it's fairly easy to create your own.
You'll need to add four fields to each of your tables to hold this information.
You'll also need to create two simple procedures and attach them to the
BeforeInsert and BeforeUpdate events of your forms.
To add this functionality to your applications, follow these
steps:
Modify your table to include four new fields, as shown in
Table 6-1.
Table 6-1. New fields for tblCustomer
Field name
Field type
Default value
DateCreated
Date/Time
=Now( )
UserCreated
Text (20)
DateModified
Date/Time
=Now( )
UserModified
Text (20)
Open your form in design view. Add new text box controls,
as shown in Table 6-2. You can place these controls anywhere on the form; they
needn't be visible. In the example form, we placed these controls along the
bottom of the form (see Figure 6-2).
Table 6-2. New controls for frmCustomer1
Control name
Control source
txtDateCreated
DateCreated
txtUserCreated
UserCreated
txtDateModified
DateModified
txtUserModified
UserModified
Set the Enabled property of these controls to No and the
Locked property to Yes. This prevents users from modifying the values that
will be computed automatically. You may also wish to set the TabStop property
of these controls to No to remove these fields from the normal tab sequence of
the form.
Create the following event procedure in the form's
BeforeInsert event, which uses the CurrentUser
function to insert the user's name. You don't need to insert the date because
it has already been supplied as a default value in the tblCustomers table.
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!UserCreated = CurrentUser( )
End Sub
Create the following event procedure in the form's
BeforeUpdate event. This time you must insert both the username and the date
and time:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!DateModified = Now( )
Me!UserModified = CurrentUser( )
End Sub
The event procedures should show up in the form's
properties sheet, as shown in Figure 6-1. Save and close the form. Open the
form and run it to test your new code.
Figure 6-1. Referencing the event procedures for
frmCustomer1
To see an example, load the frmCustomer1 form from
06-01.MDB. This form, shown in Figure 6-2, allows you to enter and edit data
in the tblCustomer table. Make a change to an existing record, and the
DateModified and UserModified fields will be updated with the current date and
time and username. Add a new record, and the DateCreated and UserCreated fields
will be updated.
Figure 6-2. The frmCustomer1 form
6.1.3 Discussion
To keep track of the username and the date and time a record
is created and updated, you must do two things:
Create additional fields in the table to hold the
information.
Create the application code to ensure that these fields are
properly updated when a record is added or modified.
We added four fields to tblCustomer: two fields to hold the
username and date/time the record was created, and another two fields to hold
the username and date/time the record was last modified. You don't have to
create all four fields, only the fields for which you wish to log information.
We also created event procedures to update these columns
whenever a record is inserted or updated. The Now
function supplies the date and time; if you'd prefer to record only the date of
the change without a time, you can use the Date
function instead. The built-in CurrentUser
function saves the name of the current user.
Access doesn't support the specification of calculated fields
at the table level, so all of the logic presented in this solution occurs at the
form level. This means that you must recreate this logic for every form that
updates the data in this table. It also means that if you add new records or
update existing records outside of a form梡erhaps by using an update query or by
importing records from another database梩he fields in Table 6-1 will not all be
automatically updated.
You can ensure that one of the fields, DateCreated, is
correctly updated for every record by adding the following expression to its
DefaultValue property:
=Now( )
Unfortunately, you can't use the DefaultValue property for
either of the updated fields, because DefaultValue is evaluated only when the
record is initially created. You can't use this property to update the
UserCreated field, either, because DefaultValue cannot call built-in or
user-defined functions (except for the special Now
and Date functions).
You may have noticed that placing the four controls from
Table 6-2 on the form takes up a considerable amount of screen space.
Fortunately, you don't need controls to make this technique work, because Access
lets you refer to a form's record-source fields directly. In the sample database
you'll find a second version of the form, frmCustomer2, that demonstrates this
variation of the technique. Notice that there are no txtDateCreated,
txtUserCreated, txtDateModified, or txtUserModified controls on frmCustomer2,
yet when you enter or edit a record using this form, the fields in tblCustomer
are correctly updated. Here's the BeforeUpdate event procedure for this form:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!DateModified = Now( )
Me!UserModified = CurrentUser( )
End Sub
Access lets you refer to fields in a form's underlying record
source (in this example, the DateModified and UserModified fields in tblCustomer)
as if they were controls on the form, even though they're not. Because of this,
it's a good idea to name the controls on a form differently from the underlying
fields. Then you can be sure that you are always referring to the correct
object.
Another consideration is that the
CurrentUser function is useful only if you have implemented user-level
security on your database. In an unsecured Access database it will always return
"Admin", which is not very informative. In that case, you can use Windows API
calls to retrieve either the computer name or the network login (or both) of the
current user, instead of the Access security account. In the sample application,
frmCustomer3 calls acbNetworkUserName when a
record is inserted or edited. Here are the API declaration and the function,
which you can find in basNetworkID:
Private Declare Function acbGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function acbNetworkUserName( ) As String
' Returns the network login name.
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = acbGetUserName(strUserName, lngLen)
If lngX <> 0 Then
acbNetworkUserName = Left$(strUserName, lngLen - 1)
Else
acbNetworkUserName = ""
End If
End Function
The basNetworkID module also includes the following API call,
which you can use to obtain the name of the current user's computer:
Private Declare Function acbGetComputerName _
Lib "kernel32" Alias "GetComputerNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
Private Const acbcMaxComputerName = 15
Public Function acbComputerName( ) As String
' Retrieve the name of the computer.
Dim strBuffer As String
Dim lngLen As Long
strBuffer = Space(acbcMaxComputerName + 1)
lngLen = Len(strBuffer)
If CBool(acbGetComputerName(strBuffer, lngLen)) Then
acbComputerName = Left$(strBuffer, lngLen)
Else
acbComputerName = ""
End If
End Function
Another option is to create your own public function called
CurrentUser that returns the network name. That
way, you won't need to change any of the code that calls
CurrentUser in your forms. Access will use your
function rather than the built-in one, and if you do implement Access security,
all you need to do is rename or remove the custom
CurrentUser function to have the form code start retrieving Access
security names using the built-in CurrentUser
function.
No comments:
Post a Comment