Access JumpStart 2.0 | Blog

A Rapid Development Framework for Microsoft Access

TDD – 031 – Moving from Dictionary to Collection

by | Jan 12, 2024 | MS Access, MS Access VBA Coding, TDD | 0 comments

The last test I wrote which I thought would pass actually doesn’t.

I wrote a test to add 2 (the many condition) dictionary entries. I think I decided to use a dictionary for saving the changes by the field name, however, the same field name would be changed multiple times and this is what makes the many condition fail. I am trying to add the same key twice to a dictionary which results in an error.

To use a dictionary then will require me to have a unique key for each time the BeforeUpdate event is run. I could change this to a collection object. This would not require a key. OR I could try to think of a unique key for the instance. Perhaps the current time. I could review what granularity the current time (say the Now() function) resolves to and mess around with that, but will I really be accessing that as a key in a dictionary? No, not really. I think I should actually change the dictionary to a collection. This would store each set of changes. Perhaps for a particular BeforeUpdate event I can use the field names as the dictionary keys for which field got changed.

So, first thing will be to change the dictionary to a collection. One nice thing about this is that it doesn’t force a dependency on the Microsoft Scripting Runtime. The collection object I am going to use is included with VBA.

Here’s my complete new FormAuditor class code:

Option Compare Database
Option Explicit

Private WithEvents FormToAudit As Access.Form
Private pListOfChanges As New Collection

Public Property Get ListOfChanges() As Collection
    Dim retVal As Collection
    Set retVal = pListOfChanges
    Set ListOfChanges = retVal
End Property

Private Sub Class_Initialize()
    Set FormToAudit = Forms("TestForm")
    FormToAudit.BeforeUpdate = "[Event Procedure]"
End Sub

Private Sub FormToAudit_BeforeUpdate(Cancel As Integer)
    pListOfChanges.Add FormToAudit.TestText.Value
End Sub
Visual Basic

Now I will need to update the tests to use Collections instead of dictionaries.

'@TestMethod("Count Changes")
Private Sub WhenNoFieldIsChangedThenReturnEmptyListOfChanges()
    Dim testFormAuditor As New FormAuditor
    Dim testCollection As New Collection
    Set testCollection = testFormAuditor.ListOfChanges
    Assert.AreEqual CLng(0), testCollection.Count
End Sub

'@TestMethod("Count Changes")
Private Sub WhenOneFieldIsChangedThenReturnSingleListOfChanges()
    Dim testFormAuditor As FormAuditor
    Dim testCollection As New Collection
    Set testFormAuditor = New FormAuditor
    Randomize Timer
    NewForm.TestText = "New Thing " & Rnd()
    NewForm.Dirty = False
    Set testCollection = testFormAuditor.ListOfChanges
    Assert.AreEqual CLng(1), testCollection.Count
End Sub

'@TestMethod("Count Changes")
Private Sub WhenTwoFieldsAreChangedThenReturnTwoEntryListOfChanges()
    Dim testFormAuditor As FormAuditor
    Dim testCollection As New Collection
    Set testFormAuditor = New FormAuditor
    Randomize Timer
    NewForm.TestText = "New Thing " & Rnd()
    NewForm.Dirty = False
    NewForm.TestText = "New Thing " & Rnd()
    NewForm.Dirty = False
    Set testCollection = testFormAuditor.ListOfChanges
    Assert.AreEqual CLng(2), testCollection.Count
End Sub
Visual Basic

The database compiles and the tests… all pass! Woo-hoo!

That’ll do pig… That’ll do.

James Cromwell Shares Story Behind His Final Line in ‘Babe’

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