• Choose a category
• All
• Classic-Fiction

By Root 1957 0

of the examples in this chapter take advantage of the DAO type library, rather than the default ADO library used by Access 2002 and Access 2003. Even though it's less "modern," DAO provides greater functionality, and generally better performance. In addition, using DAO makes it possible for these demonstrations to work in earlier versions of Access. If you want to try these techniques in your own applications, make sure you add the DAO reference to your project using the Tools → References menu item from within VBA—it won't be added by default.

6.1. Save with Each Record the Name of the Last Person Who Edited It and the Date and Time

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?

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

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.