Tracking Access Button Clicks

I am working with an older Access application that has almost 500 command buttons, and our task is to determine which features are still being used & which ones can be retired. Hopefully we'll recover screen space, plus improve the maintenance by removing obsolete queries & reports.

Of course, asking the users which features they are using is one option, but with over 100 users that is not really practical.

So we created some code to track button clicks, and using the Screen object in Access makes that quite simple. Basically we created a module to save the click info to a table, and then used Word to mass-update the code modules to add a line to invoke the tracking code. More on that later.

The ClickTracker code:

First, we test if the user clicked a command button. This is necessary because you can have a click event on a list box, combo box or even the form itself. The test looks like this:

If Screen.ActiveControl.ControlType = acCommandButton Then
' ( code goes in here )
End If

Then we can get the relevant info:

The form name is
Screen.ActiveForm.Name

The button name is
Screen.ActiveControl.Name

...and the button caption is
Screen.ActiveControl.Caption

The complete module can be viewed here. ( see * and ** below )

Part 2 - adding that line of code in 500 places...

With that many command buttons, we needed a quick way to insert that code. Easy. Select the text of the entire code module & copy / paste into Word. Then you can do a Find & Replace All, using some of the special characters available in Word:

Find this: _Click()
Replace with: _Click()^p^tClickTracker

...which will insert a new line right at the beginning of every click event to invoke the ClickTracker code. Then you just select all the text from Word & paste it back into the code module & overwrite the old version.

----
* our table uses an Autonumber for the first field, that why we skip rs.Fields(0). The other fields can be text(64) because that is the max length of a form or control name in Access.

** our version includes calls to custom functions 1: GetUser() calls the Windows API to get the ID of the Windows user, and 2: DateNoTime() strips off the time portion from Now() so we are saving only the date, not the time. You don't need these for the rest of it to work, but you do need to modify the code to skip those last two fields.