• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

Track User Usage per control


I have a series of large Access databases (hooked into SQL backend) that are used as primary ERP within an organization.

Thousands of forms and reports built up over 12 years.

I need to do a major tidy up job and find out which form controls and reports are actually being used.

I can easily create a table and stick code on each control on each form to record into the table userID, date, dbname, formname, objectname - but that's obviously a massive job to manually add this code to every single button/dropdown in the databases.

Is there something that can do all this automatically??

Many thanks to anyone who can help.
1 Solution
Well the best you can do is extending the VisualBasic Development Environment.  So in fact what you probabla want to do is writing a program for that, which does the proper modifcations.
Maybe the following link give you an idea on where to start:

It's not that "impossible" but will take some good time. You are kind of lucky that you can access all the controls of any form with a program.....
There may be third party add ins that will collect the info but I have not found one yet.
As for adding code to every moduleI think it has to be done manually.
My only suggestion would be to have a table with just a code number and count.

Create a module to with the parameter (ID as integer)   - Each control has a different number
e.g. Click_Count(1)   for the first Button   Click_count(2) for the next control    etc.

In the module do a Dlookup  for the ID  and if it is there then add 1 to the count.
If not create the record and make the count 1.

So in the button or control click/ enter/ load   etc. code all you would need is  a call Click-Count(THE ID Number).

Yes you would need a list of ID's and the controls but after a couple of weeks or so you could see which ones were missing  and thus not used.

BUt rather than deleting them  just change the names so they have an X as the first letter.  Then if there was an rerror sometime because it was called you would only have to take off the "x" and the application would work.

I was looking for something similar a few years ago but never found a n easy way to do it.

MAybe there is noew and I just have not found it.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There is always Total Access Analyzer: http://www.fmsinc.com/MicrosoftAccess/BestPractices.html. It will generally tell you where everything is being used via the XREF feature.

Also Find and Replace: http://www.rickworld.com/products.html. Won't do as complete a job of analyzing, but invaluable for making changes to your system.

If you're looking for a way to insert code into the application that will flag when a user actually uses a control, then that would be a monumental task. Better to start at the object level - add code to your Forms and Report OPen or Load events that would add a simple line to a table, something like this:

Sub Form_Open()
  Currentdb.Execute "INSERT INTO tWatcher(ObjectName, DateOpened) VALUES('" & Me.Name & "',#" & Date() &"#")
  < other code here if needed >
End Sub

Run your app in this configuration for a week or two, or however long you feel is appropriate, then examine the tWatcher table to determine which objects are used, and which are not. From there, you could then move down to the Control level.

Note that you can add a "hook" class to a form that will trap your control events. This is an advanced method, and requires a good bit of VBA knowledge and can cause performance issues, but it can be done.
I think the fms analyzer tells you where the objects are and where in the code they are referenced.
I think rcampbell01 wants to know if the objects are clicked, opened or used in the running application.
and how often or how many times
Again I do not know if there is an add in that keeps track of controls as they are activated.

The only way I know of is to code them.

One way:

Form: _Form
RecordSource: _Table (id, nm)

Form Controls: Combo0, Combo1, Combo2, id, nm

Create a class module
save it as clsControl

Replace contents with the following code:
You can extend the events with any event you want to track.

Option Compare Database
Option Explicit

Dim WithEvents clsComboBox As Access.ComboBox
Dim WithEvents clsTextBox As Access.TextBox
Dim WithEvents clsForm As Access.Form

Public Function init(frmControl As Variant, Optional frmName As String)
    If frmControl.Name = frmName Then
        Set clsForm = frmControl
        clsForm.AfterUpdate = "[Event Procedure]"
        clsForm.BeforeUpdate = "[Event Procedure]"
        If frmControl.ControlType = acComboBox Then
            Set clsComboBox = frmControl
            clsComboBox.AfterUpdate = "[Event Procedure]"
            clsComboBox.BeforeUpdate = "[Event Procedure]"
        End If
        If frmControl.ControlType = acTextBox Then
            Set clsTextBox = frmControl
            clsTextBox.AfterUpdate = "[Event Procedure]"
            clsTextBox.BeforeUpdate = "[Event Procedure]"
        End If
    End If
End Function
Private Sub clsComboBox_AfterUpdate()
  MsgBox "This is the combo After Update event " & clsComboBox.Name
End Sub
Private Sub clsComboBox_BeforeUpdate(Cancel As Integer)
  MsgBox "This is the combo before update event " & clsComboBox.Name
End Sub
Private Sub clsTextBox_AfterUpdate()
  MsgBox "This is the text After Update event " & clsTextBox.Name
End Sub
Private Sub clsTextBox_BeforeUpdate(Cancel As Integer)
  MsgBox "This is the text before update event " & clsTextBox.Name
End Sub
Private Sub clsForm_AfterUpdate()
  MsgBox "This is the form After Update event " & clsForm.Name
End Sub
Private Sub clsForm_BeforeUpdate(Cancel As Integer)
  MsgBox "This is the form before update event " & clsForm.Name
End Sub

Open in new window

Create the table _table (id, nm)
Create a form _form1
Record Source: _Table ( add fields id, nm)
Create 3 combo boxes: Combo0, Combo1, Combo2 (list values 1, 2,3)
In design view, Set form's On Open event to [Event Procedure]
OPen the form module, and replace with the following code:

Option Compare Database
Option Explicit
Dim frmComboBox() As clsControl
Dim frmTextBox() As clsControl
Dim frmForm As clsControl
' author hnasr

Private Sub Form_Open(Cancel As Integer)
    Set frmForm = New clsControl
    frmForm.init Me, Me.Name
    Dim i As Integer
    Dim ctl As Control
    i = -1
    For Each ctl In Me.Controls
        If ctl.ControlType = acComboBox Then
            i = i + 1
            ReDim Preserve frmComboBox(i)
            Set frmComboBox(i) = New clsControl
            frmComboBox(i).init Me(ctl.Name)
        End If
    i = -1
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
            i = i + 1
            ReDim Preserve frmTextBox(i)
            Set frmTextBox(i) = New clsControl
            frmTextBox(i).init Me(ctl.Name)
        End If
End Sub

Open in new window

Here is a sample database implementing the track controls issue:
try modifying combos, and text boxes.
move to a new record.


Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now