Track User Usage per control

Posted on 2011-10-06
Last Modified: 2013-11-28

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.
Question by:rcampbell01
    LVL 24

    Expert Comment

    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.....
    LVL 11

    Expert Comment

    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.
    LVL 84
    There is always Total Access Analyzer: It will generally tell you where everything is being used via the XREF feature.

    Also Find and Replace: 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.
    LVL 11

    Expert Comment

    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.

    LVL 30

    Accepted Solution

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now