I have a set of custom event handlers for all my textboxes and combo boxes. The reason for this is my users want the bound form (to SQL Server) to immediately push changes to the recordset, rather than the normal "Save" idea of an Access form. This is the constraint I have to deal with.
As a result, what I am doing is in each form open method I initialize event handlers for the form. I could not find a way to basically override the "default control event handler" and instead have to override each event handler for each type.
In each of the form_current event I want this functionality for I have:
Which points to the following method:
Public Sub mCustom_FormCurrent(ByRef p_form As Form) Set m_EventHandlerManager = New EventHandlerManager m_EventHandlerManager.initFormHandlers p_form.Form End Sub
This sets up a custom event handler for all objects. I put this on the
Public Sub initFormHandlers(p_form As Form) Dim ctl 'types - http://msdn.microsoft.com/en-us/library/office/aa224135(v=office.11).aspx Dim txt As EH_TextBox Dim cbo As EH_ComboBox Set m_TextBoxes = New Collection Set m_ComboBoxes = New Collection For Each ctl In p_form.Controls Select Case ctl.ControlType Case acTextBox: Set txt = New EH_TextBox txt.init ctl, p_form m_TextBoxes.Add txt Case acComboBox: Set cbo = New EH_ComboBox cbo.init ctl, p_form m_ComboBoxes.Add cbo Case default: End Select Next ctl End Sub
I have two classes related to this. I structured the EventHandlerManager in such a way as to allow easy addition of other form controls which are also bound (right now I am only concerned with ComboBoxes and TextBoxes).
Option Compare Database Option Explicit Private WithEvents m_cboBox As Access.ComboBox Private m_frm As Access.Form Private Const Evented As String = "[Event Procedure]" Public Sub init(p_src, ByRef p_frm As Access.Form) 'As Access.TextBox Set m_frm = p_frm Set m_cboBox = p_src m_cboBox.OnChange = Evented End Sub Private Sub m_cboBox_Change() On Error GoTo m_cboBox_Change_Error Dim i As Integer i = 0 Application.Echo False m_frm.Recordset.Edit m_frm.Recordset.Update Application.Echo True On Error GoTo 0 Exit Sub m_cboBox_Change_Error: 'This resolves an error which happens sometimes - not sure why Resume works but it fixes the .Update causing errors ??? If i = 0 Then i = i + 1 Resume Else Application.Echo True sendErrorEmail "m_cboBox_Change for " & m_cboBox.Name, err.Description, err.Number, ERROR_DEBUG End If End Sub
Option Compare Database Option Explicit Private WithEvents m_txtBox As Access.TextBox Private m_frm As Access.Form Private Const Evented As String = "[Event Procedure]" Public Sub init(p_src, ByRef p_frm As Access.Form) 'As Access.TextBox Set m_frm = p_frm Set m_txtBox = p_src m_txtBox.OnExit = Evented m_txtBox.OnChange = Evented m_txtBox.BeforeUpdate = Evented m_txtBox.AfterUpdate = Evented End Sub Private Sub m_txtBox_AfterUpdate() On Error GoTo m_txtBox_AfterUpdate_Error Dim i As Integer i = 0 m_frm.Recordset.Edit m_frm.Recordset.Update On Error GoTo 0 Exit Sub m_txtBox_AfterUpdate_Error: 'This resolves an error which happens sometimes - not sure why Resume works but it fixes the .Update causing errors ??? If i = 0 Then i = i + 1 Resume Else sendErrorEmail "m_txtBox_AfterUpdate for " & m_txtBox.Name, err.Description, err.Number, ERROR_DEBUG End If End Sub
What I am looking for
How can I make this better? This feels like a huge hack, which it kind of is, but barring the use case which is causing this how can I better create custom event handlers for all form controls? I'd have to make a class for each for control I want one for. In my situation there are only two, so it's not "that bad."
Ideally, the form controls would all be inherited from a
BaseFormControl class (I made the name up) I could override the
AfterUpdate for in a generic sense, and then apply to all form objects. This does not seem to exist though.