Microsoft Access VBA Macro Programming

This sets up a link to the functionGetUserNamein the dynamic link library advapi32

(which is supplied as part of the Windows operating system).

Next, enter a function to return the user name. Enter the following code in the same module:

Public Function ReturnUserName()

Dim strUser As String, X As Integer

strUser = Space$( 256 )

X = GetUserName(strUser, 256 )

strUser = RTrim(strUser)

ReturnUserName = Left(strUser, Len(strUser) - 1 )

End Function

The functionReturnUserNamewill always provide the windows login of the current

user. Because this is a public function, it can be used in all the form modules.

The Audit Trail on the Table Structure

For the purposes of this example, I am using the Northwind database and the Customers

table. You can access the Northwind database by loading Access and clicking Sample in the

center Navigation pane (Available Templates) and then clicking the Northwind icon. Add the

AuditTrail field to the Customers table.

On each table where you want an audit trail, you must add an extra field to hold the audit

trail information. Right-click the table name in the Navigation pane of Access and choose

design mode. Add a field as Memo type calledAuditTrail. Save the table design.

I have chosen a Memo type field so as to demonstrate how you can record every change a

user makes to the data. However, this may increase the size of the database too much and you

may wish to use a standard text field instead, one that holds less information.

Using Events to Create the Audit Trail

Your table will act as the data feed for a form. In the case of the Customers table, the

Customers List form is a convenient one to use. Open this form in Design mode and

right-click the form. Click Build Event in the pop-up menu and click Code Builder in the

following window.

In the VBE module for the form, click the drop-down in the top-left corner of the module

and click Form.

Click the drop-down in the top-right corner and click AfterUpdate. Insert the following

code into this event:

Dim RecSet As Recordset, Temp As Variant

Set RecSet = CurrentDb.OpenRecordset("select * from Customers where ID=" & Me.ID)

290 Microsoft Access 2010 VBA Macro Programming