Access JumpStart 2.0 | Blog

A Rapid Development Framework for Microsoft Access

TDD – 066 – Extracting a case statement, then do what?

by | Mar 20, 2024 | MS Access, MS Access VBA Coding, TDD | 0 comments

Per the last writing, I wanted to refactor the BeforeUpdate routine Select Case statement. Here is the code in question:

Private Sub FormToAudit_BeforeUpdate(Cancel As Integer)
    Dim Fld As Variant, ChangeDictionary As New Scripting.Dictionary, Ctl As Access.Control
    For Each Fld In FormToAudit
        Set Ctl = Fld
        Select Case Ctl.ControlType
            Case acComboBox, acTextBox, acCheckBox, acOptionGroup
            Case Else: GoTo SkipLoop
        End Select
        If FieldChanged(Ctl) Then ChangeDictionary.Add Ctl.Name, CreateAuditFieldChange(Ctl)
SkipLoop:
    Next Fld
    If ChangeDictionary.Count > 0 Then pListOfChanges.Add CreateAuditEventDetails(ChangeDictionary)
End Sub
Visual Basic

Now we need to check the Ctl.ControlType against some kind of list. What could I use that would allow me to quickly check the ControlType against a variable set of ControlTypes. I could use an array and loop over it. I could use a dictionary and set the valid types as indexes then search the dictionary for the index. Seems kind of like overkill.

Well, I guess the first step would simply be to extract the Select Case to a function like this:

Private Sub FormToAudit_BeforeUpdate(Cancel As Integer)
    Dim Fld As Variant, ChangeDictionary As New Scripting.Dictionary, Ctl As Access.Control
    For Each Fld In FormToAudit
        Set Ctl = Fld
        If Not IsControlTypeTracked(Ctl.ControlType) Then GoTo SkipLoop
        If FieldChanged(Ctl) Then ChangeDictionary.Add Ctl.Name, CreateAuditFieldChange(Ctl)
SkipLoop:
    Next Fld
    If ChangeDictionary.Count > 0 Then pListOfChanges.Add CreateAuditEventDetails(ChangeDictionary)
End Sub

Private Function IsControlTypeTracked(CType As AcControlType) As Boolean
    Dim retVal As Boolean
    Select Case CType
        Case acComboBox, acTextBox, acCheckBox, acOptionGroup: retVal = True
        Case Else: retVal = False
    End Select
    IsControlTypeTracked = retVal
End Function
Visual Basic

So far this does not solve the problem at all, but it does at least put it into a function where we can deal with just that statement. Another option here would be to use polymorphism and create an interface of some kind with a factory that would return an object based on the type passed to it. I think that’s a bit of overkill at this point. That type of solution would be more useful if the different types had very different properties needing to be checked to determine Old and New values of the control.

So for now, maybe we’ll just try a collection. I’ll go down that road next time.

Sign up For a Daily Email Adventure in Microsoft Access

Every business day (typically M-F), I'll send you an email with information about my ongoing journey as an advanced Access application developer. It will be loaded with my tips and musings.

    We won't send you spam. Unsubscribe at any time.
    Built with Kit

    Submit a Comment