Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 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:

1. Modify your table to include four new fields, as shown in Table 6-1.

2. 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).

3. 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.

4. 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

5. 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

6. 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 formperhaps by using an
update query or by importing records from another databasethe
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 GetUserName 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 = GetUserName(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 GetComputerName _
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(GetComputerName(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.

6.1.4 See Also

For more information on using DAO in Access databases, see
How Do I Use Data Access Objects (DAO) in New
Databases? in the Preface.