Active Control Change Event - MS Access

Question

I'm looking for a MS-Access form event which can check if the active control on the form has changed to another control; when it does a small script runs.

The function must be one which runs only when the form is active (such as a click on the form, etc). However, Form_Click() doesn't work as it somehow is not the same window.. I don't know what's going on there. Form_Click() also only works if you click form pieces, not controls (such as the Record Selector). This method should work for all controls with one method, not one method per control.

my code:

Private Sub <<Form_ActiveHasChanged()>>
  desc = Forms(Me.Form.Name).Controls(Me.ActiveControl.Name).StatusBarText
  Me.txtInfo.Caption = desc
End Sub

where <<Form_ActiveHasChanged()>> is my event.. is there a way to do this? I can't use timers as if the user navigates away from the form, the Me.ActiveControl is no longer in the window and throws an error. Or, if anybody knows a way to check:

If (Me.Form IS IN ACTIVE WINDOW) Then ....

Problem is I want it to work across all controls on the form; I have upwards of 30 controls in some forms (numerous tabs). Also I believe Lost_Focus() also suffers from the "the control is not in the active window" error. I'll clarify my question a bit more. — StuckAtWork, May 18 '12 at 15:35

score 3 · Accepted Answer · answered May 18 '12 at 20:23

You could do this via a class module using WithEvents. Unfortunately, there are no events attached to the generic Control object, so you will have to specify a handler for each different type of control. I've included three common controls to get you started.

Create a new class module named weControlChange and paste the following code into it. Then follow the usage comments at the top of the class module to implement.

' Usage: 1. Add the following to the declaration section of the form module:
'               Dim ControlChange As New weControlChange
'        2. Add the following to the Form_Load OR Form_Open event:
'               ControlChange.Setup Me.Form
Option Compare Database
Option Explicit

Private WithEvents weTextBox As TextBox
Private WithEvents weComboBox As ComboBox
Private WithEvents weCheckBox As CheckBox

Private CtlColl As Collection

Public Sub Setup(Frm As Form)
Dim Ctl As Control, CtlChng As weControlChange
    Set CtlColl = New Collection
    For Each Ctl In Frm.Section(acDetail).Controls 
   'For Each Ctl In Frm.Controls    ''to include controls from all sections'
        Select Case Ctl.ControlType
        Case acTextBox, acComboBox, acCheckBox
            If Ctl.Enabled And Ctl.Visible Then
                Set CtlChng = New weControlChange
                Set CtlChng.Control = Ctl
                CtlColl.Add CtlChng
            End If
        End Select
    Next Ctl
End Sub

Public Property Set Control(ByVal Ctl As Control)
    Select Case Ctl.ControlType
    Case acTextBox
        Set weTextBox = Ctl
        weTextBox.OnEnter = "[Event Procedure]"
    Case acComboBox
        Set weComboBox = Ctl
        weComboBox.OnEnter = "[Event Procedure]"
    Case acCheckBox
        Set weCheckBox = Ctl
        weCheckBox.OnEnter = "[Event Procedure]"
    End Select
End Property

Private Sub weCheckBox_Enter()
    MyScript weCheckBox
End Sub

Private Sub weComboBox_Enter()
    MyScript weComboBox
End Sub

Private Sub weTextBox_Enter()
    MyScript weTextBox
End Sub

Private Sub MyScript(Ctl As Control)
    'Your code goes here
End Function

Private Sub Class_Terminate()
Dim Ctl As Object
On Error Resume Next
    If Not CtlColl Is Nothing Then
        For Each Ctl In CtlColl
            Set Ctl = Nothing
        Next Ctl
        Set CtlColl = Nothing
    End If
End Sub

score 2 · Answer 2 · answered May 18 '12 at 21:04

2

You could create a single event handler, store it in a module, and then simply set that to be the OnExit event for every control individually. Just Ctrl-A to select them all, open the Properties dialog, and set the OnExit event. Take about 20 seconds.

answered May 18 '12 at 21:04

CarlF

226
4
13

Late comment, but this should work too.. I didn't know you could multi-select and batch set properties like that. +1 for new information! – StuckAtWork May 22 '12 at 13:26

score 0 · Answer 3 · answered May 21 '12 at 07:27

0

The easiest way to do things is to use OnEnter event handler for every control.

answered May 21 '12 at 07:27

MaxD

1
1

Active Control Change Event - MS Access

3 Answers3

Linked