Some people have asked me to cover the subject of Class Modules more, so I thought I’d start by demonstrating how we can take some form code and convert it all over to Class Module so we could then easily implement the exact same behavior on multiple forms with ease by using form Subclassing techniques.

In my last post I demonstrated how we could alter Microsoft Access’ default behavior of always saving any change so that instead it relied on the user explicitly pressing a Save button otherwise changes were not committed.  If you aren’t familiar with it, then I’d urge you to quickly review it:

In this post, I wanted to demonstrate how we could take that same code and convert it over to a Class module, or Class modules and show you how easy it then becomes to implement on any form.

Class Modules

Well, to make this happen we need to review and understand our original code to identify exactly what needs to happen.  There are 2 key ‘components’ at work here, Form level events & button events.  Thus, I created 2 Class modules: (1) to subclass the Form, (2) to subclass each command button of interest.

Form Class Module

I started by creating a Class modules named ‘Cls_Form’ with the following code:

Option Compare Database
Option Explicit

Private WithEvents oForm      As Access.Form
Private oCommandButton        As Cls_Controls_CommandButton
Private collCtrls             As VBA.Collection
Private frm_bSaveRecord        As Boolean
Const EventProcedure = "[Event Procedure]"


Public Property Get p_Form() As Access.Form
    Set p_Form = oForm
End Property

Public Property Set p_Form(ByRef oMyForm As Access.Form)
    Set oForm = oMyForm
    Call Class_init
End Property

Private Sub Class_init()
    Dim ctrl                  As Control

    Set collCtrls = New Collection
    For Each ctrl In oForm.Controls
        Select Case ctrl.ControlType
            Case acCommandButton
                If ctrl.Name = "cmd_Save" Or ctrl.Name = "cmd_Cancel" Then
                    Set oCommandButton = New Cls_Controls_CommandButton
                    Set oCommandButton.p_CommandButton = ctrl
                    Call oCommandButton.Init(Me)
                    oCommandButton.p_CommandButton.OnClick = EventProcedure
                    collCtrls.Add oCommandButton
                    Set oCommandButton = Nothing
                End If
        End Select
    Next
    Set ctrl = Nothing


    oForm.OnCurrent = EventProcedure
    oForm.AfterUpdate = EventProcedure
    oForm.BeforeUpdate = EventProcedure
    oForm.OnDirty = EventProcedure
End Sub

Private Sub Class_Terminate()
    Set collCtrls = Nothing
    Set oForm = Nothing
End Sub





' *******************************
'           Form Events
' **************************************************************
Public Property Get p_frm_bSaveRecord() As Boolean
    p_frm_bSaveRecord = frm_bSaveRecord
End Property

Public Property Let p_frm_bSaveRecord(ByVal bValue As Boolean)
    frm_bSaveRecord = bValue
End Property

Private Sub oForm_AfterUpdate()
    Call ResetSave
End Sub

Public Sub oForm_BeforeUpdate(Cancel As Integer)
    If frm_bSaveRecord = False Then
        If vbNo = MsgBox("This record has been modified.  Would you like to save the changes?", _
                         vbYesNo Or vbQuestion, "Save Current Changes?") Then
            oForm.Undo
        End If
    End If
End Sub

Private Sub oForm_Current()
    Call ResetSave
End Sub

Private Sub oForm_Dirty(Cancel As Integer)
    frm_bSaveRecord = False
    oForm.cmd_Save.Enabled = True
    oForm.cmd_Cancel.Enabled = True
End Sub


' *******************************
'           Procs
' **************************************************************
Public Sub ResetSave()
    frm_bSaveRecord = True
    oForm.cmd_Save.Enabled = False
    oForm.cmd_Cancel.Enabled = False
End Sub

Command Button Class Module

Then I started working on the class module to handle the 2 command buttons (Save and Cancel). This one I named ‘Cls_Controls_CommandButton’ and the code is:

Option Compare Database
Option Explicit

Private WithEvents m_CommandButton As Access.CommandButton
Private m_frm As Access.Form
Private m_Parent_Cls As Cls_Form


Public Sub Init(ByRef oForm_Cls As Cls_Form)
    Set m_Parent_Cls = oForm_Cls
    Set m_frm = m_Parent_Cls.p_Form
End Sub

Public Property Get p_CommandButton() As Access.CommandButton
    Set p_CommandButton = m_CommandButton
End Property

Public Property Set p_CommandButton(oCommandButton As Access.CommandButton)
    Set m_CommandButton = oCommandButton
End Property

Private Sub Class_Terminate()
    Set m_CommandButton = Nothing
End Sub



Private Sub m_CommandButton_Click()
    Select Case m_CommandButton.Name
        Case "cmd_Cancel"
            If m_frm.Dirty = True Then m_frm.Undo
            m_Parent_Cls.ResetSave
        Case "cmd_Save"
            m_Parent_Cls.p_frm_bSaveRecord = True
            If m_frm.Dirty = True Then m_frm.Dirty = False
    End Select
End Sub

Setting Up Your Form To Use The Class(es)

Now, the Class module do all the heavy lifting now, but we still need to initialize/call them in the form so all of this work as it should. To do so, we simply do:

Option Compare Database
Option Explicit

Private listener              As New Cls_Form


Private Sub Form_Close()
    Set listener = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
    Set listener.p_Form = Me
End Sub

That’s it!

Now, the form code is minimal. All that you need for this to work is that the form has the 2 required buttons (cmd_Save and cmd_Cancel) and the above code.

You can now add this to all your forms and instantaneously they will all gain this behavior.

If you want to alter the behavior, change the buttons appearance …, it can now all be done from the class and it will be pushed out to all the forms using the class(es) without you needing to do anything else but edit the class.
 

Applying Formatting

In the above, I simply use the class modules to replicate the behavior, but one great thing about using class modules is that we can use them to apply formatting to ensure standardization throughout our application.

To illustrate this just a little, below is a modified version of the ‘Cls_Controls_CommandButton’ class module’s Init procedure in which I have now added code to stylize the Save and Cancel button to ensure they look the same in each form.

Public Sub Init(ByRef oForm_Cls As Cls_Form)
    Set m_Parent_Cls = oForm_Cls
    Set m_frm = m_Parent_Cls.p_Form
    
    'Just to demonstrate how easy it is to apply formatting!
    Select Case m_CommandButton.Name
        Case "cmd_Cancel"
            m_CommandButton.BackColor = 12106214    'Accent 2, Lighter 60%
            m_CommandButton.FontWeight = 900        'thin = 100 => Heavy = 900
        Case "cmd_Save"
            m_CommandButton.BackColor = 10213059    'Accent 3, Lighter 40%
            m_CommandButton.FontWeight = 900        'thin = 100 => Heavy = 900
    End Select
End Sub

So as you can see from the above, once you have the class module framework in place, it becomes child’s play to make changes. So instead of editing 10 forms and adding such code, or editing their properties directly, I can merely add 8 lines of code to the Class module and be 100% sure that all my forms are the same (well these buttons at least). You can apply properties to all your controls or just a select few as I have done in this case. So this can be very versatile and you control just what get impacted, or not.

Obviously, we can take this much, much further (font, pictures, captions, positioning, …) and we can do the same with the form’s class module to make all our form look and feel the same. Once you start down the path of subclassing it becomes addictive and hard to stop!

Like everything else, Class Modules & SubClassing are tools. You need to know when to use them and when not to. They add another abstraction layer to any project and can make it harder for other developers that may one day take over your work. Class modules can take more time to develop initially (until you get a good grasp of the way they work), but greatly simplify making changes once implemented, thus normally saving time in the long run.

I hope this helps a few of you out there understand some of the basics involved in Class Modules and SubClassing in Microsoft Access.
 

A Simpler Approach

Some mentioned that it could all be done in a single Class Module and they’re not wrong. I like breaking things into a Module per object/control as I find it is easier to maintain, update and troubleshoot.

With that said, for our example, a single Class Module would simplify things. Like with anything VBA, it can be done in multiple way, below is 1 of those:

Class Module

Option Compare Database
Option Explicit

Private WithEvents oForm      As Access.Form
Private WithEvents BtnSave    As Access.CommandButton
Private WithEvents BtnCancel  As Access.CommandButton
Const EventProcedure = "[Event Procedure]"

Private frm_bSaveRecord       As Boolean


Public Property Get p_Form() As Access.Form
    Set p_Form = oForm
End Property

Public Property Get p_BtnSave() As Access.CommandButton
    Set p_BtnSave = BtnSave
End Property

Public Property Get p_BtnCancel() As Access.CommandButton
    Set p_BtnCancel = BtnCancel
End Property

Public Property Set p_Form(ByRef oMyForm As Access.Form)
    Set oForm = oMyForm
End Property

Public Property Set p_BtnSave(ByRef MySaveBtn As Access.CommandButton)
    Set BtnSave = MySaveBtn
End Property

Public Property Set p_BtnCancel(ByRef MyCancelBtn As Access.CommandButton)
    Set BtnCancel = MyCancelBtn
End Property

Public Sub Class_init(ByRef oMyForm As Access.Form, ByRef MySaveBtn As Access.CommandButton, ByRef MyCancelBtn As Access.CommandButton)
    Set p_Form = oMyForm
    Set p_BtnCancel = MyCancelBtn
    Set p_BtnSave = MySaveBtn

    BtnSave.OnClick = EventProcedure
    BtnCancel.OnClick = EventProcedure

    'Form Formatting
    oForm.Section(acHeader).Height = 0.5 * 1440
    oForm.Section(acHeader).BackColor = 14136213    '10213059

    oForm.Section(acDetail).BackColor = 16777215

    oForm.Section(acFooter).Height = 0.5 * 1440
    oForm.Section(acFooter).BackColor = 4210752

    'Form Events
    oForm.OnCurrent = EventProcedure
    oForm.AfterUpdate = EventProcedure
    oForm.BeforeUpdate = EventProcedure
    oForm.OnDirty = EventProcedure
End Sub

Private Sub Class_Terminate()
    Set oForm = Nothing
End Sub



' *******************************
'           Form Events
' **************************************************************
Public Property Get p_frm_bSaveRecord() As Boolean
    p_frm_bSaveRecord = frm_bSaveRecord
End Property

Public Property Let p_frm_bSaveRecord(ByVal bValue As Boolean)
    frm_bSaveRecord = bValue
End Property

Private Sub oForm_AfterUpdate()
    Call ResetSave
End Sub

Public Sub oForm_BeforeUpdate(Cancel As Integer)
    If frm_bSaveRecord = False Then
        If vbNo = MsgBox("This record has been modified.  Would you like to save the changes?", _
                         vbYesNo Or vbQuestion, "Save Current Changes?") Then
            oForm.Undo
        End If
    End If
End Sub

Private Sub oForm_Current()
    Call ResetSave
End Sub

Private Sub oForm_Dirty(Cancel As Integer)
    frm_bSaveRecord = False
    oForm.cmd_Save.Enabled = True
    oForm.cmd_Cancel.Enabled = True
End Sub



' *******************************
'           Control Events
' **************************************************************
Private Sub BtnCancel_Click()
    If oForm.Dirty = True Then oForm.Undo
    Call ResetSave
End Sub

Private Sub BtnSave_Click()
    frm_bSaveRecord = True 'Enable saving
    If oForm.Dirty = True Then oForm.Dirty = False
End Sub



' *******************************
'           General Helper Procs
' **************************************************************
Public Sub ResetSave()
    frm_bSaveRecord = True
    oForm.cmd_Save.Enabled = False
    oForm.cmd_Cancel.Enabled = False
End Sub

Form Code

Assuming we named the class module ‘Cls_Form’ then we would do something like the following in the form.

Option Compare Database
Option Explicit

Private listener              As New Cls_Form


Private Sub Form_Close()
    Set listener = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
    listener.Class_init Me, Me.cmd_Save, Me.cmd_Cancel
End Sub

and there you have it all in one Class Module.

For simple use cases, this is probably the easiest way to implement things, but at a certain point, when you are trying to manage lots of different controls, … it makes mores sense to break things down into separate class module. One way or another, you are the master of your VBE after all and can do whatever makes your happy! I just hope this helps demystify Module Classes and Microsoft Access SubClassing a little.
 

Page History